Re: [GENERAL] Error: column host does not exist
No. there are no name clashes. I actually changed the names in posted text a bit. The arguments, declared variables and column names, all have their unique prefixes. Thanks Mayuresh Reg Me Please wrote: IS there any name clash with a function argument? -- Fahrbahn ist ein graues Band weisse Streifen, grüner Rand On Thursday 08 January 2009 08:30:07 Mayuresh Nirhali wrote: Hello, I am working with 8.1.4 pgsql as my database backend. I have a function written in plpgsql language, that queries a particular table as below, select host from table_host where ip_address = ip_array[i] and port = port_array[i]; The query is running in a for loop. I see ip_array and port_array getting populated with correct values during the execution. The error I see is, ERROR: column host does not exist CONTEXT: SQL statement SELECT host PL/pgSQL function sp_get_ip_data line 286 at raise throu psql, I can select the table_host fine. column host is a primary key here. I dont understand why I see the column does not exist error; any idea ? cheers Mayuresh -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Error: column host does not exist
Hello, I am working with 8.1.4 pgsql as my database backend. I have a function written in plpgsql language, that queries a particular table as below, select host from table_host where ip_address = ip_array[i] and port = port_array[i]; The query is running in a for loop. I see ip_array and port_array getting populated with correct values during the execution. The error I see is, ERROR: column host does not exist CONTEXT: SQL statement SELECT host PL/pgSQL function sp_get_ip_data line 286 at raise throu psql, I can select the table_host fine. column host is a primary key here. I dont understand why I see the column does not exist error; any idea ? cheers Mayuresh -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres benchmark?
Craig Ringer wrote: David Siebert wrote: I am interesting in finding a good Postgres benchmark. I an not interested in seeing how fast Postgres is compared to MySql, Firebird, or any other SQL database. What I am interested in is how file systems, memory, and X-64 vs X-32 effects the performance of Postgres. It is more for my own curiosity to be honest. Right now Postgres is more than fast enough for what I am doing. Do you want to use PostgreSQL for any particular task? If so, the best benchmark is probably one that simulates your specific workload. Comparing generic benchmark results like pgbench etc may not usefully reflect performance in real-world use with your load and your data. and If you are running Solaris/OpenSolaris, DTrace is your friend. Mayuresh -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Performance problems with Postgresql/ZFS/Non-global zones on Solaris?
Bob, First, Which exact version of S10 are you using ? In general, I have seen having logs and data on different pools helps. Also, let us know your wal tunning parameters like commit_delay, fsync. If you are using S10u4, setting commit_delay to a non zero number should help get better performance. Rgds Mayuresh [EMAIL PROTECTED] wrote: In the hopes that someone has already blazed this trail ... I'm running Postgresql (v8.1.10) on Solaris 10 (Sparc) from within a non-global zone. I originally had the database storage in the non-global zone (e.g. /var/local/pgsql/data on a UFS filesystem) and was getting performance of X (e.g. from a TPC-like application: http://www.tpc.org). I then wanted to try relocating the database storage from the zone (UFS filesystem) over to a ZFS-based filesystem (where I could do things like set quotas, etc.). When I do this, I get roughly half the performance (X/2) I did on the UFS system. I did try to run some low level I/O tests (i.e. http://www.iozone.org/) to see if there was a filesystem performance difference, but there doesn't seem to be any between the UFS and ZFS numbers I'm seeing. So, I was hoping someone might have already tried this type of setup and can provide some suggestions for helping boost the ZFS performance numbers (and save me a truss debugging session). Are there any known issues with using Postgresql and ZFS? Things I have already tried: - setting the record size of the ZFS filesystem to be 8K (vs 128K ) default -- no noticeable performance difference - addind the ZFS filesystem as a loopback (i.e. lofs) filesystem in the non-global zone -- no noticeable difference - adding the ZFS filesystem as a dataset filesystem in the non-global zone -- no noticeable difference Bob ---(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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Online Oracle to Postgresql data migration
Josh Harrison wrote: Thanks . We have around 3TB of data now running in Oracle. I have done replication in postgresql but not much in Oracle. Is there a way you can replicate between Oracle and Postgresql. For writing the custom codes do you suggest any preferred language ...like java, perl etc? See, if this can help, https://daffodilreplicator.dev.java.net/ ... and do let us know if you find it useful. Rgds Mayuresh Thanks Josh ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Online Oracle to Postgresql data migration
Josh Harrison wrote: My question is abt the data migration. Im not sure how to try this with an online oracle database. We are required to run both postgres and oracle database simultaneously for a couple of months (atleast till we decide whether we are going to shut down oracle for good !!!). Since the oracle database is a production database, It will have updates/inserts during this time. How do you manage that? Thanks josh Check out ora2pg. You can use this perl tool for data migration as well. http://pgfoundry.org/projects/ora2pg/ Also, While you are converting your DDL and other queries, you could run 'explain analyze' on them to see how good they perform and make appropriate changes for better performance. Btw, glad to see that you are planning to move to PG. :) Mayuresh ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] unable to createuser in postgres 8.2.5 opensolaris
[EMAIL PROTECTED] wrote: I initialized a database directory and it started fine. But when I tried to create user I got this error. Can you tell what is wrong? thanks I did a quick check on my Solaris box and it seems to be working fine for me. Can you provide more information on what exactly you are trying to do ? Did you compile 8.2.5 yourself or picked up binaries from somewhere ? Which OpenSolaris build are you using ?? cheers Mayuresh createuser --superuser --createdb --createrole -P postgres Enter password for new role: Enter it again: createuser: could not connect to database postgres: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. the logfile is here: LOG: could not bind IPv6 socket: Cannot assign requested address HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. LOG: database system was shut down at 2007-11-18 19:50:38 PST LOG: checkpoint record is at 0/42CC84 LOG: redo record is at 0/42CC84; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 0/593; next OID: 10820 LOG: next MultiXactId: 1; next MultiXactOffset: 0 LOG: database system is ready LOG: setsockopt(TCP_NODELAY) failed: Option not supported by protocol ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] anybody attending FOSS.in 2007 ??
Hello, http://foss.in/2007 , the most happening Open Source event in India is taking place in Bangalore between Dec4th and Dec8th. Is anybody from the postgres community planning to attend ? I thought it would be a good opportunity to get together and talk about Postgres and if we have some good interest then we can even propose a BOF session or something. What say?? Cheers Mayuresh ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Use of PROFILE in Makefiles
Hello, I could not build pg8.2.4 on Solaris x86 because the variable PROFILE was set in my path to desktop profile script. Pg makfiles (Makfile.global) appends value of PROFILE if set to CFLAGS. I was wondering if there is any particular reason for having PROFILE appended to CFLAGS. If this setting is platform specific then will it help moving that to platform specific makefiles ?? Thanks Mayuresh ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
