[GENERAL] Please help... syntax Error in create set ..
I want to create new set,
I am running create set command as follows
create set (id=2, origin=1, comment='All tables');
but it gives error
syntax error near unexpected token `('
can anybody tell me where i am doing wrong
With regards
Ashish
Cheap talk?
Check out Yahoo! Messenger's low PC-to-Phone call rates.
http://voice.yahoo.com
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] [Slony1-general] Please help... syntax Error in create set ..
Hi,
On Thu, 2006-11-30 at 00:12 -0800, Ashish Karalkar wrote:
create set (id=2, origin=1, comment='All tables');
but it gives error
syntax error near unexpected token `('
That command must be passed to slonik, not to bash.
Here is an example:
#!/bin/bash
slonik _END_
cluster name = testcluster;
node 1 admin conninfo = 'dbname=test1 host=192.168.2.3 port=5432
user=postgres';
node 2 admin conninfo = 'dbname=test2 host=192.168.2.4 port=5432
user=postgres';
create set (id=1, origin=1, comment='test tables');
echo 'Set created';
set add table (set id=1, origin=1, id=1, full qualified name =
'public.test_table', comment='test table');
_END_
Regards,
--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
signature.asc
Description: This is a digitally signed message part
Re: [GENERAL] [Slony1-general] Please help... syntax Error in create set ..
Thanks for your repaly
I have done with that but facing the same problem.
Actually what i want to do is to add a table to a
already existing replication set.
firstly, I will have to create new set
secondly subscrib it .
and finaly merge it.
do i need to create this set in the same cluster into
which i will merge it later on.
If yes, how to do it coz with the slonik script it
gives me error cluster already exists.
Please help.
Thanks
--- Devrim GUNDUZ [EMAIL PROTECTED] wrote:
Hi,
On Thu, 2006-11-30 at 00:12 -0800, Ashish Karalkar
wrote:
create set (id=2, origin=1, comment='All tables');
but it gives error
syntax error near unexpected token `('
That command must be passed to slonik, not to bash.
Here is an example:
#!/bin/bash
slonik _END_
cluster name = testcluster;
node 1 admin conninfo = 'dbname=test1
host=192.168.2.3 port=5432
user=postgres';
node 2 admin conninfo = 'dbname=test2
host=192.168.2.4 port=5432
user=postgres';
create set (id=1, origin=1, comment='test tables');
echo 'Set created';
set add table (set id=1, origin=1, id=1, full
qualified name =
'public.test_table', comment='test table');
_END_
Regards,
--
The PostgreSQL Company - Command Prompt, Inc.
1.503.667.4564
PostgreSQL Replication, Consulting, Custom
Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG -
http://www.commandprompt.com/
Do you Yahoo!?
Everyone is raving about the all-new Yahoo! Mail beta.
http://new.mail.yahoo.com
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] postgresql 8.2 rc1 - crash
hi, i have been testing 8.2 rc1, while i got this problem. base data: linux, 32bit, kernel: 2.6.18.3; debian postgresql version: PostgreSQL 8.2rc1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5(Debian 1: 3.3.5-13) problematic table is over 2gigabytes in size, and has several indices - one of them is gin-index. problem: when i issue vacuum full verbose analyze it works, but then crashes with signal 11. always in the same situation. i was not able to check what is the reason. i did: recompile with debug, set ulimit -c unlimited, and rerun the query. it crashed. i bundled: 1. logs 2. core file 3. config of postgresql 4. saved output of vacuum all of this can be fetched from: http://depesz.com/various/crash.data.tar.bz2 unfortunatelly i'm not c programmer, so i dont know gdb, but i hope you will be ab le to make any sense out of it. the bz2 file is 20mb in size. any help? is it hardware problem? or a missed bug in code? if i can provide you with more information - please tell me what i should tell you. depesz -- http://www.depesz.com/ - nowy, lepszy depesz
[GENERAL] Float8 precision problem
Hi all, I got problem in postgrsql 8.0 when subtraction in float8, this is my query : select 6.1::float8-6::float8 Result 0.096 I need the result like natural subtraction is 0.1 Help me please Do you Yahoo!? Everyone is raving about the all-new Yahoo! Mail beta. http://new.mail.yahoo.com
Re: [GENERAL] Float8 precision problem
And (indeed) that is exactly the answer that you received [within DLB_DIG units of precision]. I guess that you will be happier with NUMERIC(precision, scale) because the results of operations will be closer to what you expect. Suggested reading: http://www.physics.ohio-state.edu/~dws/grouplinks/floating_point_math.pd f From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Hengki Suhartoyo Sent: Thursday, November 30, 2006 2:56 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Float8 precision problem Hi all, I got problem in postgrsql 8.0 when subtraction in float8, this is my query : select 6.1::float8-6::float8 Result 0.096 I need the result like natural subtraction is 0.1 Help me please Want to start your own business? Learn how on Yahoo! Small Business. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] postgresql 8.2 rc1 - crash
While I'm downloading your file, pls, do follow: gdb /usr/local/pgsql/bin/postgres your_core_file If it's needed, change path to postgres file. In gdb, type # bt and send output hubert depesz lubaczewski wrote: hi, i have been testing 8.2 rc1, while i got this problem. base data: linux, 32bit, kernel: 2.6.18.3 http://2.6.18.3; debian postgresql version: PostgreSQL 8.2rc1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5 (Debian 1:3.3.5-13) problematic table is over 2gigabytes in size, and has several indices - one of them is gin-index. problem: when i issue vacuum full verbose analyze it works, but then crashes with signal 11. always in the same situation. i was not able to check what is the reason. i did: recompile with debug, set ulimit -c unlimited, and rerun the query. it crashed. i bundled: 1. logs 2. core file 3. config of postgresql 4. saved output of vacuum all of this can be fetched from: http://depesz.com/various/crash.data.tar.bz2 unfortunatelly i'm not c programmer, so i dont know gdb, but i hope you will be ab le to make any sense out of it. the bz2 file is 20mb in size. any help? is it hardware problem? or a missed bug in code? if i can provide you with more information - please tell me what i should tell you. depesz -- http://www.depesz.com/ - nowy, lepszy depesz -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Float8 precision problem
On Nov 30, 2006, at 19:56 , Hengki Suhartoyo wrote: I got problem in postgrsql 8.0 when subtraction in float8, this is my query : select 6.1::float8-6::float8 Result 0.096 I need the result like natural subtraction is 0.1 Then use numeric instead of float. Float by its very nature is not precise. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 1: 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] Erorr in running create set command
Hello All, I am trying to add a new table to an running replication system. For that i want to create new set but it is giving me error. slonik _EOF_ create set (id=2, origin=1, comment='All qsweb tables'); _EOF_ stdin:1: ERROR: syntax error at or near create can anybody tell me how can i create new set in the same schema in which it will be merge later on do i need to create new set in the same schema? i have tried in creating new schema but then while merging it give me syntax error as follows slonik _EOF_ MERGE SET ( ID = 2,ADD ID = 1, ORIGIN = 1 ); _EOF_ stdin:1: ERROR: syntax error at or near MERGE thanks regards ashish karalkar Yahoo! Music Unlimited Access over 1 million songs. http://music.yahoo.com/unlimited ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] postgresql 8.2 rc1 - crash
On 11/30/06, Teodor Sigaev [EMAIL PROTECTED] wrote: gdb /usr/local/pgsql/bin/postgres your_core_file If it's needed, change path to postgres file. In gdb, type # bt and send output sure, here you have: $ gdb /home/pgdba/work/bin/postgres /home/pgdba/data/core GNU gdb 6.3-debian Copyright 2004 Free Software Foundation, Inc. GDB is free software, covered by the GNU General Public License, and you are welcome to change it and/or distribute copies of it under certain conditions. Type show copying to see the conditions. There is absolutely no warranty for GDB. Type show warranty for details. This GDB was configured as i386-linux...Using host libthread_db library /lib/tls/libthread_db.so.1. Core was generated by `postgres: trader_ru_tomcat tra'. Program terminated with signal 11, Segmentation fault. warning: current_sos: Can't read pathname for load map: Input/output error Reading symbols from /usr/lib/i686/cmov/libssl.so.0.9.7...done. Loaded symbols for /usr/lib/i686/cmov/libssl.so.0.9.7 Reading symbols from /usr/lib/i686/cmov/libcrypto.so.0.9.7...done. Loaded symbols for /usr/lib/i686/cmov/libcrypto.so.0.9.7 Reading symbols from /lib/tls/libcrypt.so.1...done. Loaded symbols for /lib/tls/libcrypt.so.1 Reading symbols from /lib/tls/libdl.so.2...done. Loaded symbols for /lib/tls/libdl.so.2 Reading symbols from /lib/tls/libm.so.6...done. Loaded symbols for /lib/tls/libm.so.6 Reading symbols from /lib/tls/libc.so.6...done. Loaded symbols for /lib/tls/libc.so.6 Reading symbols from /lib/ld-linux.so.2...done. Loaded symbols for /lib/ld-linux.so.2 Reading symbols from /lib/tls/libnss_files.so.2...done. Loaded symbols for /lib/tls/libnss_files.so.2 Reading symbols from /home/pgdba/work/lib/postgresql/tsearch2.so...done. Loaded symbols for /home/pgdba/work/lib/postgresql/tsearch2.so #0 0xb7ce4a85 in memmove () from /lib/tls/libc.so.6 (gdb) bt #0 0xb7ce4a85 in memmove () from /lib/tls/libc.so.6 #1 0x080bc224 in PageDeletePostingItem (page=0xb28039a0 \020, offset=53719) at gindatapage.c:291 #2 0x080bf558 in ginDeletePage (gvs=0xbfc2ab80, deleteBlkno=29194, leftBlkno=29059, parentBlkno=70274, myoff=351, isParentRoot=0 '\0') at ginvacuum.c:268 #3 0x080bf95c in ginScanToDelete (gvs=0xbfc2ab80, blkno=29194, isRoot=0 '\0', parent=0xb2df39a0, myoff=351) at ginvacuum.c:412 #4 0x080bf8f2 in ginScanToDelete (gvs=0xbfc2ab80, blkno=99489, isRoot=0 '\0', parent=0xb2b359a0, myoff=2) at ginvacuum.c:399 #5 0x080bf8f2 in ginScanToDelete (gvs=0xbfc2ab80, blkno=43, isRoot=1 '\001', parent=0xb28019a0, myoff=0) at ginvacuum.c:399 #6 0x080bfa83 in ginVacuumPostingTree (gvs=0xbfc2ab80, rootBlkno=43) at ginvacuum.c:446 #7 0x080bffd0 in ginbulkdelete (fcinfo=0xb2804768) at ginvacuum.c:638 #8 0x08259717 in FunctionCall4 (flinfo=0xfff6, arg1=2994751336, arg2=2994751336, arg3=2994751336, arg4=2994751336) at fmgr.c:1206 #9 0x08097005 in index_bulk_delete (info=0xbfc2af10, stats=0xb2804768, callback=0xb2804768, callback_state=0xb2804768) at indexam.c:573 #10 0x08143d42 in vacuum_index (vacpagelist=0xb2804768, indrel=0xa788c6a8, num_tuples=1675710, keep_tuples=0) at vacuum.c:3029 #11 0x08140c00 in full_vacuum_rel (onerel=0xa787aba0, vacstmt=0x83f74c0) at vacuum.c:1172 #12 0x08140a21 in vacuum_rel (relid=2994751336, vacstmt=0x83f74c0, expected_relkind=114 'r') at vacuum.c:1086 #13 0x08140127 in vacuum (vacstmt=0x83f74c0, relids=0x4601) at vacuum.c:397 #14 0x081da588 in PortalRunUtility (portal=0x841c160, query=0x83f7510, dest=0x83f73b8, completionTag=0xbfc2b2a0 ) at pquery.c:1063 #15 0x081da833 in PortalRunMulti (portal=0x841c160, dest=0x83f73b8, altdest=0x83f73b8, completionTag=0xbfc2b2a0 ) at pquery.c:1131 #16 0x081d9f7b in PortalRun (portal=0x841c160, count=2147483647, dest=0x83f73b8, altdest=0x83f73b8, completionTag=0xbfc2b2a0 ) at pquery.c :700 #17 0x081d526d in exec_simple_query (query_string=0x83f71a8 VACUUM FULL verbose analyze adverts;) at postgres.c:939 #18 0x081d8725 in PostgresMain (argc=4, argv=0x836c368, username=0x836c328 trader_ru_tomcat) at postgres.c:3419 #19 0x081b0216 in BackendRun (port=0x839b858) at postmaster.c:2909 #20 0x081af9ef in BackendStartup (port=0x839b858) at postmaster.c:2536 #21 0x081adaba in ServerLoop () at postmaster.c:1206 #22 0x081acf5a in PostmasterMain (argc=1, argv=0x836a508) at postmaster.c :958 #23 0x0816b3d4 in main (argc=1, argv=0x836a508) at main.c:188 (gdb) -- http://www.depesz.com/ - nowy, lepszy depesz
Re: [GENERAL] Float8 precision problem
Hi, What about cast to numeric? select 6.1::numeric-6::numeric ; ?column? -- 0.1 (1 row) Regards, Kaloyan Iliev Hengki Suhartoyo wrote: Hi all, I got problem in postgrsql 8.0 when subtraction in float8, this is my query : select 6.1::float8-6::float8 Result 0.096 I need the result like natural subtraction is 0.1 Help me please Want to start your own business? Learn how on Yahoo! Small Business. http://us.rd.yahoo.com/evt=41244/*http://smallbusiness.yahoo.com/r-index ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Stripping kernel FreeBSD - postgres
HI, I wish I optimize a kernel FreeBSD 6.1 on a server with only postgres installed. Where can I have any info about? Thanks in advantage. Enrico -- If Bill Gates had a penny for everytime Windows crashed,he'd be a multi-billionaire by now ...oh look, he already is [EMAIL PROTECTED] - Skype:sscotty71 http://www.linuxtime.it/enricopirozzi ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] postgresql 8.2 rc1 - crash
#1 0x080bc224 in PageDeletePostingItem (page=0xb28039a0 \020, offset=53719) at gindatapage.c:291 #2 0x080bf558 in ginDeletePage (gvs=0xbfc2ab80, deleteBlkno=29194, leftBlkno=29059, parentBlkno=70274, myoff=351, isParentRoot=0 '\0') at ginvacuum.c:268 Are you sure about your hardware? myoff in ginDeletePage() and offset in PageDeletePostingItem are the same variable... Pls, send to me postgres file itself - just core isn't very useful for debug. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Erorr in running create set command
On Thu, 30 Nov 2006 03:23:03 -0800 (PST), Ashish Karalkar [EMAIL PROTECTED] wrote: Hello All, I am trying to add a new table to an running replication system. Please stop posting Slony-I related questions to this list. slony1-general is the more applicable list for this, see my reply there. Thanks, Bernd ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Blob fields and backups
Hi, I have an Oracle DB, where my backup file is 280 GB and growing. I also have a lot of blob fields there. When i make a backup recover, the blob fields are there, and my boss is alive. I want to know how postgresql's backup utilities deal with blob fields... Thanks, Servidor de correo corporativo - Ximma Ltda. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Stripping kernel FreeBSD - postgres
In response to Enrico [EMAIL PROTECTED]: HI, I wish I optimize a kernel FreeBSD 6.1 on a server with only postgres installed. Where can I have any info about? http://www.freebsd.org/doc/en_US.ISO8859-1/books/handbook/kernelconfig.html It appears as if English is not your primary language, is it Italian? The FreeBSD handbook has been translated to Italian if that will make things easier for you: http://www.freebsd.org/doc/it_IT.ISO8859-15/books/handbook/kernelconfig.html -- Bill Moran Collaborative Fusion Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Stripping kernel FreeBSD - postgres
http://www.freebsd.org/doc/it_IT.ISO8859-15/books/handbook/kernelconfig.html Hi Bill I already read your link and it is not specific for Postgres, I'm searching for a more specific document. Regards, Enrico -- If Bill Gates had a penny for everytime Windows crashed,he'd be a multi-billionaire by now ...oh look, he already is [EMAIL PROTECTED] - Skype:sscotty71 http://www.linuxtime.it/enricopirozzi ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Function to replace decimal value with character ?
Hi List !
I have some data that comes from a HTTP server.
Some characters are encoded with the HTML convention :
#dec value in ISO8859-1;
I'd like to replace these sequences with the 'real'
character, my database being encoded in UTF8.
I already found that to convert the decimal value to the
corresponding character, I had to use :
convert(chr(decimal value) using iso_8859_1_to_utf8)
Now, I'd like to convert an entire string at once, so I
thought of using regexp_replace, but this does not work :
select regexp_replace(mystring, '\\#(...);',
convert(chr('\\1'::integer) using iso_8859_1_to_utf8), 'g')
Is there a way to accomplish it this way, or should I write
a more complicated plpgsql function ?
Thanks
--
Arnaud
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Stripping kernel FreeBSD - postgres
Enrico wrote: http://www.freebsd.org/doc/it_IT.ISO8859-15/books/handbook/kernelconfig.html Hi Bill I already read your link and it is not specific for Postgres, I'm searching for a more specific document. You'll probably want a lot of shared memory, but you can set that using sysctls. If you're on a multi-cpu system, you'll want SMP. If you're on a hyperthreading machine, you'll have to choose whether you want that on or off (don't know what's best for PostgreSQL). If you're on a 32-bit platform with lots of RAM, you may need PAE. Other than that, I really wouldn't know. What kind of kernel options do you expect to have any impact on database performance? You could build a couple of different kernels with different configs and see what works best for you. I suppose hyper-threading or not and PAE or not are the best candidates for experimentation. I know there're quite a few FreeBSD users among the people here and I've always kinda disliked system administration, so I suspect I'm not the best candidate for answering your question :P -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Stripping kernel FreeBSD - postgres
Enrico wrote: HI, I wish I optimize a kernel FreeBSD 6.1 on a server with only postgres installed. Where can I have any info about? Opimizing the FreeBSD kernel? Obviouly you want to start with a machine with a much memory as you can afford, as fast a disk subsystem you can afford, and as powerful a CPU you can afford. Then rip a boot only CD, and do a minimal install with the ports collection. FreeBSD 6.1 is pretty optimized already, and it will adapt. Have you followed the directions for building a custom kernel? http://www.freebsd.org/doc/en_US.ISO8859-1/books/handbook/kernelconfig-building.html Don't include hardware you don't have is obvious to strip from the GENERIC kernel configuration file, and make build for SMP if you have real dual cores or processors if you have them (a single CPU with hyperthreading most likely will not run as fast under an SMP kernel). As far as any other tweeking, follow the directions here and don't do anything http://www.freebsd.org/doc/en_US.ISO8859-1/books/arch-handbook/vm-tuning.html Then as root user cd /usr/ports/databases/postgresql81-server/ make install clean and enjoy your posgreSQL/freeBSD server. -- Walter ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Stripping kernel FreeBSD - postgres
Bill Moran wrote: In response to Enrico [EMAIL PROTECTED]: HI, I wish I optimize a kernel FreeBSD 6.1 on a server with only postgres installed. Where can I have any info about? http://www.freebsd.org/doc/en_US.ISO8859-1/books/handbook/kernelconfig.html It appears as if English is not your primary language, is it Italian? The FreeBSD handbook has been translated to Italian if that will make things easier for you: http://www.freebsd.org/doc/it_IT.ISO8859-15/books/handbook/kernelconfig.html Also something more PostgreSQL specific - http://www.postgresql.org/docs/8.1/interactive/kernel-resources.html http://lists.freebsd.org/pipermail/freebsd-performance/2004-January/000634.html -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 1: 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
Re: [GENERAL] postgresql 8.2 rc1 - crash
Teodor Sigaev [EMAIL PROTECTED] writes: #1 0x080bc224 in PageDeletePostingItem (page=0xb28039a0 \020, offset=53719) at gindatapage.c:291 #2 0x080bf558 in ginDeletePage (gvs=0xbfc2ab80, deleteBlkno=29194, leftBlkno=29059, parentBlkno=70274, myoff=351, isParentRoot=0 '\0') at ginvacuum.c:268 Are you sure about your hardware? myoff in ginDeletePage() and offset in PageDeletePostingItem are the same variable... That sort of thing isn't unusual when looking at dumps with an optimized executable. gdb has only a limited view of what the compiler is doing, and frequently will think that register N contains a variable when in fact that register gets re-used for several different purposes within the function. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] postgresql 8.2 rc1 - crash
I reproduce a problem with small script:
print EOT;
drop table if exists qq;
create table qq (
i int,
ii int[]
);
COPY qq FROM stdin;
EOT
for ($i=0;$i100;$i++) {
print $i\t{1}\n;
}
print EOT;
\\.
CREATE INDEX qqidx ON qq USING gin (ii);
DELETE FROM qq WHERE i5000 and i40;
VACUUM FULL ANALYZE qq;
EOT
So, I'm digging now...
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Need testers for 8.2 RC1 RPMs
Hello, (Sorry for the cross-posting) Today, I have built PGDG RC1 SRPM; as well as RPMs for RHEL 4 and Fedora Core 6 packages for x86 and x86_64. They will be on main FTP site for testing in an hour. I hope to upload more packages for wide testing; but these should be enough at least for now. RHEL4, Fedora Core 5 and Fedora Core 6 users will need to install additional compat RPM to satisfy dependencies of libpq.so.4. The compat RPM is provided in the same directory of each RPM set. Please follow the regular upgrade procedures. This set requires an initdb against *all* PostgreSQL versions/releases. Beginning from 8.2, we will upload only 1 SRPM. In previous versions, we were copying the SRPM to each subdirectory :-) This will save space for the mirrors and prevent confusion. For any problems please e-mail me. Regards, -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Need testers for 8.2 RC1 RPMs
Hello, (Sorry for the cross-posting) Today, I have built PGDG RC1 SRPM; as well as RPMs for RHEL 4 and Fedora Core 6 packages for x86 and x86_64. They will be on main FTP site for testing in an hour. I hope to upload more packages for wide testing; but these should be enough at least for now. RHEL4, Fedora Core 5 and Fedora Core 6 users will need to install additional compat RPM to satisfy dependencies of libpq.so.4. The compat RPM is provided in the same directory of each RPM set. Please follow the regular upgrade procedures. This set requires an initdb against *all* PostgreSQL versions/releases. Beginning from 8.2, we will upload only 1 SRPM. In previous versions, we were copying the SRPM to each subdirectory :-) This will save space for the mirrors and prevent confusion. For any problems please e-mail me. Regards, -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ libpq.so.4 is for compatibility with 8.1 If my apps work with 8.1 and need libpq.so.4, when I upgrade to 8.2 this libpq.so.4 will be talking to postgresql 8.2 without problems ? Leonel ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Stripping kernel FreeBSD - postgres
Obviouly you want to start with a machine with a much memory as you can afford, as fast a disk subsystem you can afford, and as powerful a CPU you can afford. Then rip a boot only CD, and do a minimal install with the ports collection. Done FreeBSD 6.1 is pretty optimized already, and it will adapt. Have you followed the directions for building a custom kernel? Yes. Don't include hardware you don't have is obvious to strip from the GENERIC kernel configuration file, and make build for SMP if you have real dual cores or processors if you have them (a single CPU with hyperthreading most likely will not run as fast under an SMP kernel). Done As far as any other tweeking, follow the directions here and don't do anything http://www.freebsd.org/doc/en_US.ISO8859-1/books/arch-handbook/vm-tuning.html Ok Thanks a lot Enrico -- If Bill Gates had a penny for everytime Windows crashed,he'd be a multi-billionaire by now ...oh look, he already is [EMAIL PROTECTED] - Skype:sscotty71 http://www.linuxtime.it/enricopirozzi ---(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] postgresql 8.2 rc1 - crash
Fixed, thank you. Changes are commited in CVS, pls, try it (I think that index is corrupted, so you need to recreate it) -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 1: 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
Re: [GENERAL] How to increace nightly backup speed
On Wed, Nov 29, 2006 at 11:21:41PM +0100, Bernhard Weisshuhn wrote: LZO is pretty much rock solid. It is used in OpenVPN and supposedly was used for the communication with NASAs Mars Rovers Spirit and Opportunity, if that counts as trusted. It's also GPL, which makes it a hard sell. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] IN clause in a cursor
Nevermind. I used: OPEN test FOR EXECUTE 'SELECT a, b, c FROM tbl WHERE d IN ' values; Nik wrote: I have a dynamic set of clauses that I would like to use in the cursor. Is there a way to achieve this using the IN clause and a string, or multiple OR clauses coupled with strings. This doesn't work, but it's an example of what I'm trying to do. - DECLARE test refcursor; values varchar; BEGIN -- This will be dynamically generated values := '(1, 2, 3)'; OPEN test FOR SELECT a, b, c FROM tbl WHERE d IN values; CLOSE test; END; - or - DECLARE test refcursor; values varchar; BEGIN -- This will be dynamically generated values := '(d=1 OR d=2 OR d=3)'; OPEN test FOR SELECT a, b, c FROM tbl WHERE values; CLOSE test; END; - ---(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] backend crash following load command
Merlin Moncure [EMAIL PROTECTED] writes: also, if what Martijn is saying is correct, wouldn't that make the LOAD command unsupportably dangerous? If you have write access to a file that you can LOAD, then you can already put garbage into the backend's memory space, so I don't see this as a security hole. It'd be unfortunate if true though. The mmap man page is pretty vague on the subject, but I wonder whether the shlib isn't effectively treated as copy-on-write --- that is, any attempted overwrite of the file happens only after the mmap region has been fully copied. Without that, it'd be impossible to update core shared libraries like libc.so without a system reboot, but Linux doesn't seem to need that. I suspect that this issue is specific to dlsym() and has nothing to do with the safeness of ordinary usage of a shared library. The reason 8.2 is getting bit is that it tries to do a dlsym() lookup during shlib unload, which we never did before. (Merlin, I assume you have been doing the same things with 8.1 and before without a problem?) Hmm ... would it be worth doing the lookup of _PG_fini during library load instead of unload, and saving the result? This'd be a waste of cycles if the library were never unloaded, which is much the normal case, but library load probably isn't a critical path anyway. regards, tom lane ---(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] Development of cross-platform GUI for Open Source DBs
Ritesh Nadhani wrote: Also, IDEs like Delphi etc. are out of question as I cant afford to buy the licenses. You can get Turbo Delphi Explorer for free and the license allows commercial development. It really is sweet. Check it out here: http://www.turboexplorer.com/ The explorer version come in C#, Delphi.net and native win32. The C# OR Delphi.net versions can be used to develop Mono 1.2 applications and work great with the any .net data provider assembly such as Npgsql which you can get at www.pgfoundry.org. Later, -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Ident authentication failed for user dsivam
Hi- I've run into a problem using Postgres 8.1 that has me stumped: I'm logged on as user 'postgres' and I can connect using the following command: bash$psql However when I specify the host: bash$psql -h localhost or bash$psql -h hostname.domain.com I get the following error: psql: FATAL: Ident authentication failed for user postgres I have the listen_address parameter set to * in postgresql.conf. Below are the permissions in my pg_hba.conf: # local is for Unix domain socket connections only local all all ident sameuser # IPv4 local connections: hostall all 127.0.0.1/32 ident sameuser # IPv6 local connections: hostall all ::1/128 ident sameuser Any Ideas? Thanks... ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Question about encoding
Richard Broersma Jr wrote: Is it possible to configure PostgreSQL so that a LIKE 'a' query will match a 'รก' value, ie, make it accent-insensitive ? I forgot this was possible using regular expressions. I don't think it is possible using the LIKE syntax. What a pity, I've found a point where Microsoft SQL Server is better than PostgreSQL ! :-) How should I go about filing an RFE or equivalent? TIA, Daniel Serodio ---(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] [Windows] Pg_Restore on 8.1.5 and so on...
Terry Yapt wrote: Terry Yapt wrote: I am trying to restore from a pg_dump. Pg_restore is doing some strange behaviour. If I open a CMD shell console and execute pg_restore, nothing is showed. If I try to do a with sense pg_restore, nothing is showed in spite of I have put --verbose option. This is my complete command: pg_restore --verbose --file=c:\dbt.dump --host=127.0.0.1 --username=masteruser --password Somebody with the same behaviour ?. I am running 8.1.5 on a Windows XP sp2 workstation. Thanks in advance. Same behaviour on 8.2 rc1. Any help would be very appreciated. -- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Create index on array element?
I guess one cannot create an index on an element of an array? test= create table test ( ordinal int[] ); CREATE TABLE test= create unique index x1 on test ( ordinal[1] ); ERROR: syntax error at or near ) at character 45 LINE 1: create unique index x1 on test ( ordinal[1] ); ^ Correct, or is my syntax wrong? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] unixodbc, oid and c++ error at insert
Hello.
I have a db with a table that it has a two oid fields. in the next code
i insert a new row in the table with this oid fields but it crash.
This crash is when called the function SQLParamData that it return -1
why does it crash? any ideas?
int PgsqlImageDao::insertImage(Connection* conn, const Image image,
unsigned char* previewData, unsigned int
previewSize,
Date photoDate, Date insDate,
const string info) throw(DataAccessException) {
char ODBC_ident[IDENT_SIZE];
char ODBC_info[INFO_SIZE];
unsigned char* imageData = NULL;
long ODBC_result; // Result of
function calls.
SQLHDBC ODBC_conHnd;// Handle for a
connection
SQLHSTMT ODBC_stmHnd; // Handle for a
statement
SQLINTEGER ODBC_id;
SQLINTEGER ODBC_size;
SQLINTEGER ODBC_previewSize;
SQLINTEGER ODBC_strlen = 0;
SQLINTEGER ODBC_dataPar;
SQLINTEGER ODBC_previewPar;
SQLPOINTER ODBC_token;
SQLSMALLINT ODBC_shortCols[IMAGE_SHORT_BINDS];
SQL_DATE_STRUCT ODBC_photoDate;
SQL_DATE_STRUCT ODBC_insDate;
ODBC_conHnd = conn-getHnd();
ODBC_result = SQLAllocHandle(SQL_HANDLE_STMT, ODBC_conHnd,
ODBC_stmHnd);
if ((ODBC_result != SQL_SUCCESS) (ODBC_result !=
SQL_SUCCESS_WITH_INFO)) {
delete conn;
throw SysDataAccessException(ODBC_result, Cannot allocate
statement handle);
}
/* Get new image ID. */
SQLBindCol(ODBC_stmHnd, 1, SQL_C_ULONG, ODBC_id, 1, ODBC_strlen);
ODBC_result = SQLExecDirect(ODBC_stmHnd, (SQLCHAR*) GET_ID_QUERY,
SQL_NTS);
if ((ODBC_result != SQL_SUCCESS) (ODBC_result !=
SQL_SUCCESS_WITH_INFO)) {
SQLFreeHandle(SQL_HANDLE_STMT, ODBC_stmHnd);
delete conn;
throw SysDataAccessException(ODBC_result,
Cannot execute statement GET_ID_QUERY);
}
ODBC_result = SQLFetch(ODBC_stmHnd);
if (ODBC_result == SQL_NO_DATA) {
SQLFreeHandle(SQL_HANDLE_STMT, ODBC_stmHnd);
delete conn;
throw
UserDataAccessException(UserDataAccessException::DATA_NOT_FOUND,
Cannot get a new image ID (GET_ID_QUERY));
}
ODBC_result = SQLFreeStmt(ODBC_stmHnd, SQL_CLOSE);
ODBC_result = SQLFreeStmt(ODBC_stmHnd, SQL_UNBIND);
ODBC_result = SQLFreeStmt(ODBC_stmHnd, SQL_RESET_PARAMS);
/* Set query parameters values with the image properties. */
imageData = ((Image) image).ImageB();
ODBC_shortCols[0] = image.getType();
ODBC_shortCols[1] = image.getNCol();
ODBC_shortCols[2] = image.getNLin();
ODBC_size = image.SizeOfImage();
ODBC_previewSize = previewSize;
ODBC_photoDate.year = photoDate.getYear();
ODBC_photoDate.month = photoDate.getMonth();
ODBC_photoDate.day = photoDate.getDay();
ODBC_insDate.year = insDate.getYear();
ODBC_insDate.month = insDate.getMonth();
ODBC_insDate.day = insDate.getDay();
{
const string ident = image.getIdent();
ident.copy(ODBC_ident, IDENT_LENGTH);
int i = (ident.length() IDENT_LENGTH)? ident.length():
IDENT_LENGTH;
ODBC_ident[i] = '\0';
ODBC_ident[IDENT_LENGTH] = '\0';
info.copy(ODBC_info, INFO_LENGTH);
i = (info.length() INFO_LENGTH)? info.length(): INFO_LENGTH;
ODBC_info[i] = '\0';
ODBC_info[INFO_LENGTH] = '\0';
}
ODBC_strlen = SQL_NTS;
SQLPrepare(ODBC_stmHnd, (SQLCHAR*) INSERT_IMAGE_QUERY, SQL_NTS);
SQLBindParameter(ODBC_stmHnd, 1, SQL_PARAM_INPUT, SQL_C_SLONG,
SQL_INTEGER, 0, 0, ODBC_id, 0, NULL);
SQLBindParameter(ODBC_stmHnd, 2, SQL_PARAM_INPUT, SQL_C_SSHORT,
SQL_SMALLINT, 0, 0, ODBC_shortCols[0], 0, NULL);
SQLBindParameter(ODBC_stmHnd, 3, SQL_PARAM_INPUT, SQL_C_SSHORT,
SQL_SMALLINT, 0, 0, ODBC_shortCols[1], 0, NULL);
SQLBindParameter(ODBC_stmHnd, 4, SQL_PARAM_INPUT, SQL_C_SSHORT,
SQL_SMALLINT, 0, 0, ODBC_shortCols[2], 0, NULL);
SQLBindParameter(ODBC_stmHnd, 5, SQL_PARAM_INPUT, SQL_C_SLONG,
SQL_INTEGER, 0, 0, ODBC_size, 0, NULL);
SQLBindParameter(ODBC_stmHnd, 6, SQL_PARAM_INPUT, SQL_C_TYPE_DATE,
SQL_DATE, 0, 0, ODBC_photoDate, 0, NULL);
SQLBindParameter(ODBC_stmHnd, 7, SQL_PARAM_INPUT, SQL_C_TYPE_DATE,
SQL_DATE, 0, 0, ODBC_insDate, 0, NULL);
SQLBindParameter(ODBC_stmHnd, 8, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, IDENT_LENGTH, 0, ODBC_ident, 0, ODBC_strlen);
SQLBindParameter(ODBC_stmHnd, 9, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, INFO_LENGTH, 0, ODBC_info, 0, ODBC_strlen);
SQLBindParameter(ODBC_stmHnd, 10, SQL_PARAM_INPUT, SQL_C_BINARY,
SQL_LONGVARBINARY, 0, 0, (SQLPOINTER) DATA_TOKEN, 0,
ODBC_dataPar);
SQLBindParameter(ODBC_stmHnd, 11, SQL_PARAM_INPUT, SQL_C_BINARY,
SQL_LONGVARBINARY, 0, 0, (SQLPOINTER) PREVIEW_TOKEN, 0,
Re: [GENERAL] [Windows] Pg_Restore on 8.1.5 and so on...
Terry Yapt wrote: Terry Yapt wrote: Terry Yapt wrote: I am trying to restore from a pg_dump. Pg_restore is doing some strange behaviour. If I open a CMD shell console and execute pg_restore, nothing is showed. If I try to do a with sense pg_restore, nothing is showed in spite of I have put --verbose option. This is my complete command: pg_restore --verbose --file=c:\dbt.dump --host=127.0.0.1 --username=masteruser --password Somebody with the same behaviour ?. I am running 8.1.5 on a Windows XP sp2 workstation. Thanks in advance. Same behaviour on 8.2 rc1. Any help would be very appreciated. Solved. You must use psq frontend with a plain pg_dump file. It is on documentation/manual. :-( Sorry. -- ---(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] sudden drop in delete performance
Did you 'vacuum analyze' after you did the update? Make sure you have the correct indexes in place on your foreign keys. Did you have fsync off on your previous installation? Give some more details and I'm sure people will be able to give better advice than me. Regards, Ben surabhi.ahuja [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I was using Postgres 8.0.0 I have upgraded it to Postgres 8.1.5 I have seen that the delete performance has degraded considerably. Nothing else has changed. Please help thanks regards Surabhi
Re: [GENERAL] postgresql 8.2 rc1 - crash
On 11/30/06, Teodor Sigaev [EMAIL PROTECTED] wrote: Fixed, thank you. Changes are commited in CVS, pls, try it (I think that index is corrupted, so you need to recreate it) great. thanks. i will retry. full retry will take some time - i can estimate that i will be able to reply tomorrow in the evening (my evening) - let's say - in 24 hours. hubert -- http://www.depesz.com/ - nowy, lepszy depesz
Re: [GENERAL] Development of cross-platform GUI for Open Source DBs
Ritesh Nadhani wrote: Hello All Sorry for the late reply. Been a little busy with my assignments. I will try to answer all the queries in this mail. The reason I don't want to develop the project in wxWindows or a C/C++ based toolkit is that in the end I would be able to compile a binary Another reason why suggested wxWidgets is because I have worked with it before and I am comfortable with the system. As somebody suggested, even wxPython looks good as Python greatly increases the speed of implementation. You do know that wxWindows and wxWidgets are the same thing right? The big problem with all the solutions mentioned (besides Delphi, SharpDevelop etc) is that they take a LOT of time to develop complex GUI applications with. I looked at Python etc, but it would have taken me 10 times (or more) longer to create Lightning Admin using something other than Delphi. How long do you think it would take you to create a grid with features like this: http://www.devexpress.com/Products/VCL/ExQuantumGrid/ or even like this freeware one:http://www.scalabium.com/smdbgrid.htm That is the beauty of Delphi, there are simply thousands and thousands of 3rd party components that make your life so much easier. Need a free super nice syntax highlighting editor with full support for code completion and param hinting? Check out: http://mh-nexus.de/unisynedit.htm Need unicode control support? check out: http://www.tntware.com/delphicontrols/unicode/ Just my opinion on the matter :-) No flames please. -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(end of broadcast)--- TIP 1: 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
Re: [GENERAL] Development of cross-platform GUI for Open Source DBs
The big problem with all the solutions mentioned (besides Delphi, SharpDevelop etc) is that they take a LOT of time to develop complex GUI applications with. I looked at Python etc, but it would have taken me 10 times (or more) longer to create Lightning Admin using something other than Delphi. Huh? I seriously doubt that is the case if you were to use something like Py/QT. Sure if you are command lining all the code but that is certainly not what has to happen. Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Development of cross-platform GUI for Open Source DBs
I can't speak for wxPython etc., but regarding using QT from C++, your speed of development depends on your C++ skills. I wrote a full-featured Point of Sale system in Visual Basic that has been in active use for about 4 years. The original development effort (screens, grids, etc.) took about 3 weeks. I got tired of supporting an app that I had to lug out my Windows/VB laptop for, so I decided to port it to C++ so I could develop on my Linux box. I ported the entire thing over to C++/QT in about a week. QT is *extremely* good. My speed with developing GUI applications in C++ is now on par with my abilities in VB...And I can't think of a tool on earth faster than VB for cranking out quick-n-dirty apps :P The big problem with all the solutions mentioned (besides Delphi, SharpDevelop etc) is that they take a LOT of time to develop complex GUI applications with. I looked at Python etc, but it would have taken me 10 times (or more) longer to create Lightning Admin using something other than Delphi. ---(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
[GENERAL] Separation of clients' data within a database
I have been using PostgreSQL for years in my web apps, and so far in my career I have not had to deal with database-side permissions issues. i.e. when I have multiple clients, or hands on the data, everyone interfaces through my web app and I handle security there. The app enforces what data they can or can't see/modify based on their login credentials. I have never really messed with database level permissions other than casually. I am faced with a very new problem for me, which is that my app is going to be used directly by several companies utilizing one server. (i.e. these companies will be able to go under the hood quite a bit more than we typically allow with this system). There are several issues with respective IT departments wanting to retain some level of control of their data, and I know they are not going to be satisfied simply using my web app frontend. Of course, I can't simply give them carte blanche access to the database because *I* am responsible for the integrity of the data, and also I cannot allow them to view each others' data. Since the different clients' data is currently stored in the same tables (separated by keys to the client table) I cannot simply do table-level permissions. I would assume there are no row level permissions, right? (Even the thought of it seems way too much to maintain) I have considered the following solutions to the problem: 1) Actually separate client data by table, and give each client a database user only capable of read-only access to their company's table. This seems like it would work, but it would greatly increase the complexity of my app. Although I have heard that it is possible to implement a writeable view, so perhaps I could make views which mimic my current schema. Still, seems complex. 2) Do a daily dump of the data to a different database on the same PostgreSQL server, one database for each client. The stumbling block here is that I don't think that there's any way to use pg_dump etc. to only dump some data. I considered dumping everything, and then programmatically deleting data that client should not see, but if the delete failed they have a database full of their competitor's information. 3) Similar to solution 1), except the data in the individual client tables is simply a copy of that client's data, and gets blown away every night by a scheduled copy of data. This way my app would simply operate as it does currently, and I could actually give my clients full access to their tables. 4) Create views for each client that filter the underlying table data to only show them their data. The only database objects they would have read permission on are these views. Come to think of it, this is probably the best way to go. 5) Something I haven't thought of :) Has anyone run into this sort of thing before? The IT guys in this situation love using linked tables in Access over ODBC and just copy vast quantities of data by hand, manually modifying information etc., so there's no way in hell I'm letting them touch my data. John ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Separation of clients' data within a database
On Thu, Nov 30, 2006 at 12:48 PM, in message [EMAIL PROTECTED], John McCawley [EMAIL PROTECTED] wrote: 4) Create views for each client that filter the underlying table data to only show them their data. The only database objects they would have read permission on are these views. Come to think of it, this is probably the best way to go. That's what I'd be looking at. Be sure to revoke public rights on the database and public schema, and grant back the rights you want. Think about using the schemas to segregate the views for the various clients. -Kevin ---(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] Separation of clients' data within a database
I have been using PostgreSQL for years in my web apps, and so far in my career I have not had to deal with database-side permissions issues. i.e. when I have multiple clients, or hands on the data, everyone interfaces through my web app and I handle security there. The app enforces what data they can or can't see/modify based on their login credentials. I have never really messed with database level permissions other than casually. I am faced with a very new problem for me, which is that my app is going to be used directly by several companies utilizing one server. (i.e. these companies will be able to go under the hood quite a bit more than we typically allow with this system). There are several issues with respective IT departments wanting to retain some level of control of their data, and I know they are not going to be satisfied simply using my web app frontend. Of course, I can't simply give them carte blanche access to the database because *I* am responsible for the integrity of the data, and also I cannot allow them to view each others' data. Since the different clients' data is currently stored in the same tables (separated by keys to the client table) I cannot simply do table-level permissions. I would assume there are no row level permissions, right? (Even the thought of it seems way too much to maintain) I have considered the following solutions to the problem: 1) Actually separate client data by table, and give each client a database user only capable of read-only access to their company's table. This seems like it would work, but it would greatly increase the complexity of my app. Although I have heard that it is possible to implement a writeable view, so perhaps I could make views which mimic my current schema. Still, seems complex. 2) Do a daily dump of the data to a different database on the same PostgreSQL server, one database for each client. The stumbling block here is that I don't think that there's any way to use pg_dump etc. to only dump some data. I considered dumping everything, and then programmatically deleting data that client should not see, but if the delete failed they have a database full of their competitor's information. 3) Similar to solution 1), except the data in the individual client tables is simply a copy of that client's data, and gets blown away every night by a scheduled copy of data. This way my app would simply operate as it does currently, and I could actually give my clients full access to their tables. 4) Create views for each client that filter the underlying table data to only show them their data. The only database objects they would have read permission on are these views. Come to think of it, this is probably the best way to go. 5) Something I haven't thought of :) Has anyone run into this sort of thing before? The IT guys in this situation love using linked tables in Access over ODBC and just copy vast quantities of data by hand, manually modifying information etc., so there's no way in hell I'm letting them touch my data. John ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq why don't you create a schema for every company and grant permissions to use the shchema to only the user that needs to use that?? more info : http://www.postgresql.org/docs/8.1/static/ddl-schemas.html Leonel ---(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] Separation of clients' data within a database
It seems that this approach would suffer the same problem as the one I outlined in 1) Actually separate client data by table. I would have to modify the logic of my web app...My web app currently handles all of the data, regardless of company, so it would have to aggregate the data from the different schemas when pulling data, and be smart enough to write back to the proper schemas when writing data. Leonel Nunez wrote: why don't you create a schema for every company and grant permissions to use the shchema to only the user that needs to use that?? more info : http://www.postgresql.org/docs/8.1/static/ddl-schemas.html Leonel ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Separation of clients' data within a database
search_path=$user in postgresql.conf and you create one schema for each user with the user name as name Rodrigo John McCawley wrote: It seems that this approach would suffer the same problem as the one I outlined in 1) Actually separate client data by table. I would have to modify the logic of my web app...My web app currently handles all of the data, regardless of company, so it would have to aggregate the data from the different schemas when pulling data, and be smart enough to write back to the proper schemas when writing data. Leonel Nunez wrote: why don't you create a schema for every company and grant permissions to use the shchema to only the user that needs to use that?? more info : http://www.postgresql.org/docs/8.1/static/ddl-schemas.html Leonel ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 1: 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
Re: [GENERAL] Separation of clients' data within a database
Note that my in my current code, application-level permissions are completely detached from database permissions. The entire web app uses one user/pass to login to the database. The web app is used both by individual companies who can only view their data, and also the overseeing company who is capable of viewing everything. While they are logging in with different application-level users, they are querying with the same database-level user. My question regarding database user-level permission was for the purpose of the IT departments going under the hood rather than for security in my web app. As the app is currently written, I have dropdown filters for what data the report will produce. The lesser' companies' filter forces them to view only their data (where tbl_foo.company_id = bar), whereas the overseeing company runs the same report without a filter, and the data is organized with a group by. Right now, the addition of a company is simply an addition of a row in the client table, and the app adjusts without modification. If I add a schema per company, every time I add a company I would have to modify every query in the system to also pull from this additional schema, or modify my entire application to pull from views which must be modified every time a company is added... Rodrigo Gonzalez wrote: search_path=$user in postgresql.conf and you create one schema for each user with the user name as name Rodrigo John McCawley wrote: It seems that this approach would suffer the same problem as the one I outlined in 1) Actually separate client data by table. I would have to modify the logic of my web app...My web app currently handles all of the data, regardless of company, so it would have to aggregate the data from the different schemas when pulling data, and be smart enough to write back to the proper schemas when writing data. Leonel Nunez wrote: why don't you create a schema for every company and grant permissions to use the shchema to only the user that needs to use that?? more info : http://www.postgresql.org/docs/8.1/static/ddl-schemas.html Leonel ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 1: 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Separation of clients' data within a database
Is it possible to have each user connect via different postgresql account? if so, then you can use alter user set search_path='common_schema','user_schema'; where common schema has the things that would be the same for each instance of the app, and user_schema is the name of that user's schema. Then you don't have to change your app very much, if any, and each user can only get to their schema, assuming you've only granted them permission on their own schemas (change rights) and the common_schema (usage rights) On Thu, 2006-11-30 at 13:35, Rodrigo Gonzalez wrote: search_path=$user in postgresql.conf and you create one schema for each user with the user name as name Rodrigo John McCawley wrote: It seems that this approach would suffer the same problem as the one I outlined in 1) Actually separate client data by table. I would have to modify the logic of my web app...My web app currently handles all of the data, regardless of company, so it would have to aggregate the data from the different schemas when pulling data, and be smart enough to write back to the proper schemas when writing data. Leonel Nunez wrote: why don't you create a schema for every company and grant permissions to use the shchema to only the user that needs to use that?? more info : http://www.postgresql.org/docs/8.1/static/ddl-schemas.html Leonel ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 1: 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Separation of clients' data within a database
On Thu, 2006-11-30 at 13:45, John McCawley wrote: Note that my in my current code, application-level permissions are completely detached from database permissions. The entire web app uses one user/pass to login to the database. The web app is used both by individual companies who can only view their data, and also the overseeing company who is capable of viewing everything. While they are logging in with different application-level users, they are querying with the same database-level user. My question regarding database user-level permission was for the purpose of the IT departments going under the hood rather than for security in my web app. As the app is currently written, I have dropdown filters for what data the report will produce. The lesser' companies' filter forces them to view only their data (where tbl_foo.company_id = bar), whereas the overseeing company runs the same report without a filter, and the data is organized with a group by. Right now, the addition of a company is simply an addition of a row in the client table, and the app adjusts without modification. If I add a schema per company, every time I add a company I would have to modify every query in the system to also pull from this additional schema, or modify my entire application to pull from views which must be modified every time a company is added... That's just the point of search_path. For me, it can be: alter user smarlowe set search_path='common','smarlowe'; for joe user it might be alter user joe_user set search_path='common','joe_user'; and all you have to change is the connection statement for your app depending on who logged in. voila! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Stripping kernel FreeBSD - postgres
On Nov 30, 2006, at 8:50 AM, Enrico wrote: I already read your link and it is not specific for Postgres, I'm searching for a more specific document. Just remove any devices you don't have on your machine, and remove any extras like linux compat, older version compat, etc. You probably also want to disable kernel module loading (and not build any modules). ... but the kernel is demand paged so it won't really load up the code that's not used. Personally I have a pseudo-custom configuration that covers all of my various machines and removes devices and modules I don't need. I load up USB on computers that have only USB keyboards as a module for example. You should spend more time tuning postgres itself. smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] Separation of clients' data within a database
It seems that this approach would suffer the same problem as the one I outlined in 1) Actually separate client data by table. I would have to modify the logic of my web app...My web app currently handles all of the data, regardless of company, so it would have to aggregate the data from the different schemas when pulling data, and be smart enough to write back to the proper schemas when writing data. on the public schema you can have a table with the company and schema and then select the default search path to the company's schema and the rest of your app will be untouched leonel ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Separation of clients' data within a database
Maybe I'm not understanding what you're getting at, so I'll throw out an example: -- With my current architecture, smartlowe logs in, but his login is handled at the application layer, so his database connection is simply foo. He inserts a hundred records in the invoice table, which is the public table invoice...these invoice records are automatically tagged with the client_id associated with his login. When he runs a report, the program forces a filter, aso by the client_id associated with his login. Now joe_user logs in, once again the foo db user, and inserts 100 records, tagged with his client_id. When he runs a report, it is forceably filtered by his client_id. smartlowe and joe_user are writing to the same database table, but the application forces the separation of their data and reporting. HOWEVER, when user big_daddy logs into the application, he just just run a global query on the invoice table and pull all invoices. This requires no special knowledge by the app of what clients exist in the system...The SQL query handles the organization of the report by company. -- I don't understand how I could implement what you're describing without massive changes to my existing (5+ years in development) application. Even factoring out that there are literally hundreds of people actually logging into this system, I will just address a hypothetical if each company only logs in with one user. company_a logs in and inserts 100 records into tbl_invoice which automagically becomes company_a.tbl_invoice. In his report, this also automagically becomes company_a.tbl_invoice. company_b logs in and inserts 100 records into tbl_invoice which automagically becomes company_b.tbl_invoice. In his report, this also automagically becomes company_b.tbl_invoice. big_daddy logs in and wants to view *all* invoice data. In the invoice report, this becomes big_daddy.tbl_invoice, which has nothing in it because big daddy doesn't invoice. He wants the data from all of the different companies. How would the system know to aggregate the company_a.tbl_invoice UNION company_b.tbl_invoice UNION (etcetera) As I said, we're talking about a pretty big system here, I don't have the luxury of gutting the entire thing. Of course, I may just not understand what I'm talking about with schemas, but that's why I'm asking ;) Scott Marlowe wrote: That's just the point of search_path. For me, it can be: alter user smarlowe set search_path='common','smarlowe'; for joe user it might be alter user joe_user set search_path='common','joe_user'; and all you have to change is the connection statement for your app depending on who logged in. voila! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Ident authentication failed for user dsivam
On Tue, Nov 28, 2006 at 03:40:09PM -0800, [EMAIL PROTECTED] wrote: Hi- I've run into a problem using Postgres 8.1 that has me stumped: I'm logged on as user 'postgres' and I can connect using the following command: bash$psql However when I specify the host: bash$psql -h localhost snip Maybe you're not running an ident daemon? Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
[GENERAL] Any issues w/PostgreSQL 8.0.4 on Win2K3 x64?
Is anyone aware of any issues with running PostgreSQL 8.0.4 on a Windows 2003 Server x64 system? (I know it's an old DB version that we will eventually upgrade to 8.1.x or 8.2.x, but it's running on a production system where for the immediate future I don't have the time to test on any of the later versions.) I've seen discussions re: running 8.1.x w/o problem on the x86 versions of Windows server, but I wanted to check to see if anyone had any experience (good or bad) running 8.0.x on x86 Windows. Thanks! - Bill ---(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] Any issues w/PostgreSQL 8.0.4 on Win2K3 x64?
On Thu, 2006-11-30 at 15:23 -0500, Bill Bartlett wrote: Is anyone aware of any issues with running PostgreSQL 8.0.4 on a Windows 2003 Server x64 system? You will want to check the release notes for differences between 8.0.4 and 8.0.9 but except for the fact that PostgreSQL on win32 doesn't currently support 64bit mode, I think you should be fine. Joshua D. Drake (I know it's an old DB version that we will eventually upgrade to 8.1.x or 8.2.x, but it's running on a production system where for the immediate future I don't have the time to test on any of the later versions.) I've seen discussions re: running 8.1.x w/o problem on the x86 versions of Windows server, but I wanted to check to see if anyone had any experience (good or bad) running 8.0.x on x86 Windows. Thanks! - Bill ---(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 -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Separation of clients' data within a database
On Thu, 2006-11-30 at 14:07, John McCawley wrote: Maybe I'm not understanding what you're getting at, so I'll throw out an example: I completely understand what you're saying, but I'm not quite getting the reasons for it. For instance: HOWEVER, when user big_daddy logs into the application, he just just run a global query on the invoice table and pull all invoices. This requires no special knowledge by the app of what clients exist in the system...The SQL query handles the organization of the report by company. Why does user big_daddy need to access everybody's data? Who is he? What's his role? It seems like a big security problem waiting to happen, but that's just me. I don't understand how I could implement what you're describing without massive changes to my existing (5+ years in development) application. Even factoring out that there are literally hundreds of people actually logging into this system, I will just address a hypothetical if each company only logs in with one user. company_a logs in and inserts 100 records into tbl_invoice which automagically becomes company_a.tbl_invoice. In his report, this also automagically becomes company_a.tbl_invoice. company_b logs in and inserts 100 records into tbl_invoice which automagically becomes company_b.tbl_invoice. In his report, this also automagically becomes company_b.tbl_invoice. So far, everything seems fine. No changes in your app needed but for logins big_daddy logs in and wants to view *all* invoice data. In the invoice report, this becomes big_daddy.tbl_invoice, which has nothing in it because big daddy doesn't invoice. He wants the data from all of the different companies. How would the system know to aggregate the company_a.tbl_invoice UNION company_b.tbl_invoice UNION (etcetera) And again the weird requirement that your data be segregated for most users, but then not be segregated for others. For a database to do that, you'd need per row permissions and postgresql doesn't do that, at least not natively. You could maybe make some kind of view / rule / trigger system that checked each row against some master permissions table. But performance wise you're just asking for trouble once you're under heavy load doing that. As I said, we're talking about a pretty big system here, I don't have the luxury of gutting the entire thing. Of course, I may just not understand what I'm talking about with schemas, but that's why I'm asking ;) Well, I think if you're willing to write some extra code for the supervisor role to be able to view everything, schemas make a lot of sense. I imagine you could make some schema that has those unions you mention above for reading all the data, and a user with usage permission on all the schemas to run reports. you could even aggregate multiple companies in different ways if need be (i.e. company_a has two subdivisions, you give them a single view of the two subdivisions with a single user log in to review all the data at once) but don't let anyone else see their schemas. This is one of those fundamental problems you run into when you make a design decision up front (user perms in the app) and some change in architecture (users in charge of web servers) changes your whole security model. You really don't have a lot of choice at this point. You've pretty much GOT to put the security in some layer below the web/app server, because you can't trust those to do the right thing anymore if you don't control them. I've found myself in your position before. Maybe you would be better off writing some middleware layer that the front end hits. I.e. split your web app in half. Front half asks back half to do something, you maintain the back half locally. That idea might or might not be too hare-brained depending on your situation. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Development of cross-platform GUI for Open Source DBs
On 11/30/06, John McCawley [EMAIL PROTECTED] wrote: I can't speak for wxPython etc., but regarding using QT from C++, your speed of development depends on your C++ skills. I wrote a full-featured Point of Sale system in Visual Basic that has been in active use for about 4 years. The original development effort (screens, grids, etc.) took about 3 weeks. I got tired of supporting an app that I had to lug out my Windows/VB laptop for, so I decided to port it to C++ so I could develop on my Linux box. I ported the entire thing over to C++/QT in about a week. QT is *extremely* good. My speed with developing GUI applications in C++ is now on par with my abilities in VB...And I can't think of a tool on earth faster than VB for cranking out quick-n-dirty apps :P having used both, I can tell you that delphi is much better than vb for developing database applications. you also can use the quixotic hybrid in c++ builder which is a curious mixture of rad technology and c++ power. however with each release since delphi 5 or so, borland seems to be unable to do much more than add bugs to the product, and so the market has moved on. also the kylix project was a complete failure. btw, I agree that qt is where it's at. great stuff. merlin ---(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] [pgsql-general] Separation of clients' data within a database
On Thu, 2006-30-11 at 17:22 -0400, [EMAIL PROTECTED] wrote: Date: Thu, 30 Nov 2006 12:48:53 -0600 From: John McCawley [EMAIL PROTECTED] To: pgsql-general@postgresql.org Subject: Separation of clients' data within a database Message-ID: [EMAIL PROTECTED] ... I would assume there are no row level permissions, right? (Even the thought of it seems way too much to maintain) You could take a look at Veil http://veil.projects.postgresql.org/ which gives you row-level access controls. Whatever solution you choose has its problems though: 1) Veil You have to manage user permissions, implement a bunch of access functions and secured views, and add connection functions to your sessions. 2) Separate databases You have to manage separate databases 3) Separate schemas You have to manage the separate schemas, and also consider whether access to the underlying catalogs is allowed (making it impossible for one client to infer the existence of another may be important to you). __ Marc signature.asc Description: This is a digitally signed message part
[GENERAL] JDBC traffic logger
I am trying to measure amount of data coming from Postgres DB to application layer. Is there any tool/utility that can help to log all results using JDBC driver? I would really appreciate your help on this. Regards, Ranjan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Separation of clients' data within a database
Why does user big_daddy need to access everybody's data? Who is he? What's his role? It seems like a big security problem waiting to happen, but that's just me. Uncle Sam :) This is one of those fundamental problems you run into when you make a design decision up front (user perms in the app) and some change in architecture (users in charge of web servers) changes your whole security model. Well, you're right, the security model has changed. The situation is that the system was written for one company to manage its clients, and the permission model was basically company/client, and the client had pared-down access enforced by the app (the security model is quite a bit more refined than that, but I'm simplifying)...The problem domain has expanded for there to be many companies (clients no longer really exist), and one over-arching super-company able to view everything. Note that I am retaining 100% control of the Web-App server and the database server (i.e. no one else will have superuser abilities), but I know that the different companies will want the ability to connect to the database under the hood. I think the most effective solution will be to simply create a database user for each company, and for each company create a series of views, owned by that user, which are hard-wired to view only their data. Of course I still have to modify my web app and schema to facilitate the new security structure, but I was never too worried about handling it in my app...My concern was allowing people direct access to the underlying DB while a) blocking them from viewing others' data, and b) without having to drastically modify the fundamental structure of my app. ---(end of broadcast)--- TIP 1: 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
Re: [GENERAL] Create index on array element?
On Wed, 2006-11-29 at 14:39 -0600, Dave Bodenstab wrote: I guess one cannot create an index on an element of an array? test= create table test ( ordinal int[] ); CREATE TABLE test= create unique index x1 on test ( ordinal[1] ); ERROR: syntax error at or near ) at character 45 LINE 1: create unique index x1 on test ( ordinal[1] ); ^ You need more parenthesis. test= create unique index x1 on test ( ( ordinal[1] ) ); The reason is because ordinal[1] is an expression, similar to ordinal [1] + 5 which would also need parenthesis. Hope this helps, Jeff Davis ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] initdb problem on Windows XP Home
Hi Again, I posted earlier about a problem installing 8.1.5 on Windows XP Home. THat message was delayed, so it likely will not show up. THe problem is occuring on execution of initdb. I skipped the cluster creation in the installation so that I could run it manually, but it made no difference. The contents of the temp file for the initdb is: The files belonging to this database system will be owned by user postgres. This user must also own the server process. The database cluster will be initialized with locale C. fixing permissions on existing directory C:/Program Files/PostgreSQL/8.1/data ... ok creating subdirectories ... initdb: could not create directory C:/Program Files: File exists initdb: removing contents of data directory C:/Program Files/PostgreSQL/8.1/data I tried specifying a folder that doesn't exist, and then I get a permissions error. Fine, I logged into the system under the restricted account and tried running initdb again. However, same results. I tried specifying a folder that doesn't already exist, but of course the restricted user does not have access to the C: drive directly. If I create a folder under C:, e.g. C:\postgres_db_cluster, and then try to run initdb -D C:\postgres_db_cluster I get the same results, except this time the log reads creating subdirectories ... initdb: could not create directory C:/postgres_db_cluster: File exists initdb: removing contents of data directory C:/postgres_db_cluster Has anyone encountered this before? If so, how do I get past this? I really need to get the database going. Any help you can offer will be most appreciated. Thank you. Alex. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] CertFirst Legit?
If you're concerned about them, you might want to just use someone else... I can think of at least 2 other companies that offer training classes. On Nov 27, 2006, at 2:40 PM, [EMAIL PROTECTED] wrote: I've been tasked with administering one of our PostgreSQL databases and know little or nothing about the product (though I do have DB experience). I've been looking for training and came across an administration class at 'http://www.postgresql.org/about/event.425' on the main web site. When I called the certfirst people offering the classes I got the impression that they weren't on the up and up. When I asked if the class in Newport Beach was sure to run they tried to talk me into taking a class in their Illinois location, and when I asked about a class in January in Vegas they told me it was already full but I could take the December class in Illinois -- but they wouldn't tell me where their classes in Vegas are held. Can anyone provide me with a reference for these guys? Also, they claim to offer a certification but it is done online using some web site I've never heard of. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL doesn't accept connections when Windows
On Nov 27, 2006, at 10:41 AM, Tony Caduto wrote: I then updated the postgresql.conf file with listen_addresses = '*' and changed the port from 5432 (currently being used for a 7.3 server) to 5435. Have you tried setting the port back to default and see what happens? Is it possible for you to stop the 7.3 server for a moment to test this theory? The only thing you have different is the port, so I am thinking that it has something to do with that. Hmm... are you sure there isn't already something listening on port 5435? -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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] Blob fields and backups
On Nov 30, 2006, at 5:15 AM, [EMAIL PROTECTED] wrote: I have an Oracle DB, where my backup file is 280 GB and growing. I also have a lot of blob fields there. When i make a backup recover, the blob fields are there, and my boss is alive. I want to know how postgresql's backup utilities deal with blob fields... Most people that deal with binary data in PostgreSQL use bytea, which to PostgreSQL is JustAnotherField. It'll dump and restore just fine. The one downside is that a lot of binary values get escaped into octal, ie '\000', which adds a lot of size to the dump. Though, the custom dump type might get around that. I think that support for large objects (which are more akin to Oracle blobs/clobs) is in pg_dump as well, but I've never actually used them. Ultimately, if you've got a 300G database, you probably don't want to be using pg_dump anyway; instead use Point In Time Recovery. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 1: 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
Re: [GENERAL] Solaris 10 problem
On Nov 27, 2006, at 11:19 PM, Doron Baranes wrote: This is exactly what i am doing. Sun Microsystems Inc. SunOS 5.10 Generic January 2005 You have new mail. # su - postgres Sun Microsystems Inc. SunOS 5.10 Generic January 2005 -bash-3.00$ id uid=49500(postgres) gid=65434(postgres) -bash-3.00$ initdb -D /var/lib/pgsql/data -bash: /usr/bin/initdb: Invalid argument Hmm... I think that's actually bash generating the error. It seems to be unhappy with initdb for some unknown reason. It might be worth trying with another shell (I think solaris installs ksh or csh by default). permissions on /usr/bin/initdb is -r-xr-xr-x 1 root bin56276 Jul 12 19:26 /usr/bin/initdb permissions on the /var/lib/pgsql/data drwx-- 2 postgres postgres 512 Nov 27 15:43 data 10x 4 the help Doron -Original Message- From: Jim Nasby [mailto:[EMAIL PROTECTED] Sent: Tue 11/28/2006 1:06 AM To: Doron Baranes Cc: pgsql-general@postgresql.org Subject: Re: Solaris 10 problem On Nov 27, 2006, at 8:59 AM, Doron Baranes wrote: I installed postgres 814 on solaris 10 using the following packages SUNWpostgr-libs-8.1.4-x86.tar.gz SUNWpostgr-8.1.4-x86.tar.gz SUNWpostgr-server-8.1.4-x86.tar.gz SUNWpostgr-server-data-8.1.4-x86.tar.gz SUNWpostgr-contrib-8.1.4-x86.tar.gz SUNWpostgr-devel-8.1.4-x86.tar.gz SUNWpostgr-docs-8.1.4-x86.tar.gz SUNWpostgr-jdbc-8.1.4-x86.tar.gz SUNWpostgr-tcl-8.1.4-x86.tar.gz SUNWpostgr-pl-8.1.4-x86.tar.gz and no error occured. when i try to run initdb i get Invalid Argument. What's the *exact* error you got, and how *exactly* did you invoke initdb? -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
