Re: [GENERAL] Cutting the Gborg throat
-Original Message- From: [EMAIL PROTECTED] on behalf of Joshua D. Drake Sent: Sun 8/27/2006 5:22 AM To: [EMAIL PROTECTED]; PgSQL General Subject: [GENERAL] Cutting the Gborg throat Question: How is CVS handled on Gborg? Do they have their own repos? If so we can just move them to pgfoundry yes? Yes - rsync is our friend. /D ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Cutting the Gborg throat
Question: How is CVS handled on Gborg? Do they have their own repos? If so we can just move them to pgfoundry yes? Yes - rsync is our friend. Cool... so can you think of any other dependencies we are misisng? 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/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Can't populate database using Copy
Try this:copy the file FinalData.txt to the 'bin' folder of pgsql where postgres binaries are located and then try doing the COPY usingcopy tabledaily from 'FinalData.txt ' with csv;and see if it solves the problem or not?-- Shoaib MirEnterpriseDB (www.enterprisedb.com)On 8/27/06, Michael Fuhr [EMAIL PROTECTED] wrote: On Sat, Aug 26, 2006 at 11:35:56AM -0700, Jim wrote: copy tabledaily from '/home/jb/FinalData.txt' with csv; ERROR:could not open file /home/jb/FinalData.txt for reading: Permission denied. Yet everything else works, including populating tables with Insert queries. All the permissions were changed for world access ( perhaps bad idea but out offrustration; and am the only person on this computer ). drwxrwxrwx 3 root root4096 Aug 21 10:19 homedrwxrwxrwx 25 jb jb 4096 Aug 26 11:18 jb-rwxrwxrwx 1 jb jb 255 Aug 25 18:15 FinalData.txtMight this be an SELinux problem? --Michael Fuhr---(end of broadcast)---TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] speeding up big query lookup
Silvela, Jaime \(Exchange\) [EMAIL PROTECTED] writes: I have a very big table that catalogs measurements of some objects over time. Measurements can be of several (~10) types. It keeps the observation date in a field, and indicates the type of measurement in another field. I often need to get the latest measurement of type A for object X. This is a pretty common requirement, and since plain SQL doesn't handle it very well, different DBMSes have invented different extensions to help. For instance you can use LIMIT: SELECT * from object_val WHERE object_id = X and object_val_type_id = Y ORDER BY object_id DESC, object_val_type_id DESC, observation_date DESC LIMIT 1; This will work very very fast if there is an index on (object_id, object_val_type_id, observation_date) for it to use. The only problem with it is that there's no obvious way to extend it to fetch latest measurements for several objects in one query. Another way, which AFAIK is Postgres-only, is to use DISTINCT ON: SELECT DISTINCT ON (object_id, object_val_type_id) * from object_val ORDER BY object_id DESC, object_val_type_id DESC, observation_date DESC This can give you all the latest measurements at once, or any subset you need (just add a WHERE clause). It's not amazingly fast but it generally beats the bog-standard-SQL alternatives, which as you mentioned require joining against subselects. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Cutting the Gborg throat
-Original Message- From: Joshua D. Drake [mailto:[EMAIL PROTECTED] Sent: 27 August 2006 11:42 To: Dave Page Cc: [EMAIL PROTECTED]; PgSQL General Subject: Re: [GENERAL] Cutting the Gborg throat Question: How is CVS handled on Gborg? Do they have their own repos? If so we can just move them to pgfoundry yes? Yes - rsync is our friend. Cool... so can you think of any other dependencies we are misisng? Not offhand - but then I only migrated 2 projects and in both cases we only did the CVS repo. /D ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Shared Objects (Dynamic loading)
Hi,Can you please give me pointers to how to establish clinet server model using PL/Perl. I mean how do i give the ip address of the database server in my Perl script running in another machine.Regards,Jas On 8/26/06, Michael Fuhr [EMAIL PROTECTED] wrote: On Sat, Aug 26, 2006 at 03:32:37PM -0400, Jasbinder Bali wrote: Do we have any concept of shared objects in perl. Just wondering, how do we dynamically load something written in perl in postgresql. A PL/Perl function can load external code with use, require,or do.Since those are potentially dangerous operations you'llneed to create the function with plperlu, which means you'll need to create the function as a database superuser.See Trusted andUntrusted PL/Perl in the documentation for more information. http://www.postgresql.org/docs/8.1/interactive/plperl-trusted.htmlRegarding use, require, and do see the Perl documentation,in particular the perlfunc and perlmod manual pages. --Michael Fuhr
Re: [GENERAL] Shared Objects (Dynamic loading)
On Sun, Aug 27, 2006 at 05:13:25PM -0400, Jasbinder Bali wrote: Can you please give me pointers to how to establish clinet server model using PL/Perl. I mean how do i give the ip address of the database server in my Perl script running in another machine. DBI is a Perl module for connecting to databases; to connect to a PostgreSQL database you'll also need DBD::Pg. If you have those modules installed then use a command like man DBI or perldoc DBI (or however you read documentation on your system) for more information. Are you creating a PL/Perl function in one database that needs to connect to a different database? What exactly are you trying to do? -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Cutting the Gborg throat
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I have been looking at the migration of Gborg lately. It looks like the only two active projects on that site are Slony, and pljava. Libpqxx has recently moved to their own Trac site. There are definitely more than that. 1. We set a read only date of 12/31/06 2. We set a shut down date of 03/31/07 I don't have a problem per se with shutting down gborg, but there are some questions to answer/problems to resolve first: 1) Have the project maintainers been sent an email yet? (as promised multiple times)? 3) Is there a migration guide or documentation somewhere? 3) Is the pgfoundry machine stable and finished being moved/upgraded/etc.? 4) Are we changing the subdomain or not? Speaking on my own behalf, this is important because if I port a project, hard-coded documentation URLs need to be changed as well. 5) Is subversion available? This is not a must, but a strong nice to have and having it would be a good indication that a) someone is actively working on the site, and b) someone is willing to help pgfoundry move forward in the future. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200608272100 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFE8kAbvJuQZxSWSsgRAhl5AKC9miWUAETzwdfZ/oL0R+c2PQDNvgCfd6hg 8iqkEakHQCDlGjtrz7uRdCI= =KeJ0 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Shared Objects (Dynamic loading)
Michael Fuhr wrote: On Sun, Aug 27, 2006 at 05:13:25PM -0400, Jasbinder Bali wrote: Can you please give me pointers to how to establish clinet server model using PL/Perl. I mean how do i give the ip address of the database server in my Perl script running in another machine. DBI is a Perl module for connecting to databases; to connect to a PostgreSQL database you'll also need DBD::Pg. If you have those modules installed then use a command like man DBI or perldoc DBI (or however you read documentation on your system) for more information. and 'perldoc Pg' for Pg specific info -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin ---(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] Shared Objects (Dynamic loading)
The actual scenario is like my perl code is on one computer and database server is on the other computer. The perl code needs to connect to that database server residing on a diff computer. I think client machine should also have DBI module in it. right? Also, how much of a change would it be if i have to migrate my triggers and functions written for C to Perl Thanks, Jas On 8/27/06, Michael Fuhr [EMAIL PROTECTED] wrote: On Sun, Aug 27, 2006 at 05:13:25PM -0400, Jasbinder Bali wrote: Can you please give me pointers to how to establish clinet server model using PL/Perl. I mean how do i give the ip address of the database server in my Perl script running in another machine.DBI is a Perl module for connecting to databases; to connect to a PostgreSQL database you'll also need DBD::Pg.If you have thosemodules installed then use a command like man DBI or perldocDBI (or however you read documentation on your system) for more information.Are you creating a PL/Perl function in one database that needs toconnect to a different database?What exactly are you trying todo?--Michael Fuhr
Re: [GENERAL] [PATCHES] New variable server_version_num
On Sat, Jul 29, 2006 at 09:14:16PM -0400, Greg Sabino Mullane wrote: Today on IRC David Fetter and some others were discussing version numbers and we realized that although libpq now provides the version of Postgres as a number, this is still a wheel that is being reinvented by apps many times over, as it is not available any other way. Hence, a small patch to provide a new variable server_version_num, which is almost the same as server_version but uses the handy PG_VERSION_NUM which allows apps to do things like if ($version = 80200) without having to parse apart the value of server_version themselves. Here's an SQL function which does the same thing. I've had it tested back through 7.4x, and it should work back to the 7.3 series, although I haven't tested it there. Thanks to Andrew of Supernews for the short version :) SELECT sum( substring( split_part( current_setting( 'server_version' ), '.' , i ) FROM '^[[:digit:]]+' )::integer * 10^(6-i*2) ) AS server_version_integer FROM ( SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 ) AS s(i); Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(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] Cutting the Gborg throat
Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I have been looking at the migration of Gborg lately. It looks like the only two active projects on that site are Slony, and pljava. Libpqxx has recently moved to their own Trac site. There are definitely more than that. ? like? I am not saying you are wrong, but I didn't see much. 1. We set a read only date of 12/31/06 2. We set a shut down date of 03/31/07 I don't have a problem per se with shutting down gborg, but there are some questions to answer/problems to resolve first: 1) Have the project maintainers been sent an email yet? (as promised multiple times)? Well that is kind of the point of this whole thread. To solidify this now and get it done (including communication with maintainers) 3) Is there a migration guide or documentation somewhere? I think you missed my point. There is no migration, except the important of the mbox files and moving of the cvs repo. All tickets etc... are the responsibility of the project. 3) Is the pgfoundry machine stable and finished being moved/upgraded/etc.? To my knowledge it has never moved but it is current stable. 4) Are we changing the subdomain or not? Speaking on my own behalf, this is important because if I port a project, hard-coded documentation URLs need to be changed as well. I don't know what you mean? 5) Is subversion available? This is not a must, but a strong nice to have and having it would be a good indication that a) someone is actively working on the site, and b) someone is willing to help pgfoundry move forward in the future. This is actually part of a thread I started on Gforge-admins. It will be available, it just isn't yet. Joshua D. Drake - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200608272100 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFE8kAbvJuQZxSWSsgRAhl5AKC9miWUAETzwdfZ/oL0R+c2PQDNvgCfd6hg 8iqkEakHQCDlGjtrz7uRdCI= =KeJ0 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- === 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/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Cutting the Gborg throat
In the last exciting episode, [EMAIL PROTECTED] (Joshua D. Drake) wrote: Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I have been looking at the migration of Gborg lately. It looks like the only two active projects on that site are Slony, and pljava. Libpqxx has recently moved to their own Trac site. There are definitely more than that. ? like? I am not saying you are wrong, but I didn't see much. 1. We set a read only date of 12/31/06 2. We set a shut down date of 03/31/07 I don't have a problem per se with shutting down gborg, but there are some questions to answer/problems to resolve first: 1) Have the project maintainers been sent an email yet? (as promised multiple times)? Well that is kind of the point of this whole thread. To solidify this now and get it done (including communication with maintainers) Consider the Slony-I group informed. :-) We're close to release of version 1.2; moving to pgFoundry is high on the ToDo list after that, which is pretty compatible with a December 31st close date. 3) Is there a migration guide or documentation somewhere? I think you missed my point. There is no migration, except the important of the mbox files and moving of the cvs repo. All tickets etc... are the responsibility of the project. Right. The ticketing system on pgFoundry is sufficiently more sophisticated that it seems to me to make more sense to manually deal with them... 5) Is subversion available? This is not a must, but a strong nice to have and having it would be a good indication that a) someone is actively working on the site, and b) someone is willing to help pgfoundry move forward in the future. This is actually part of a thread I started on Gforge-admins. It will be available, it just isn't yet. We had been putting things off based on that not yet being available, but that seemed to be a mistake. It would be nice to have Subversion, but I'm disinclined to hold my breath... -- wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','acm.org'). http://linuxfinances.info/info/slony.html --Despite Pending :Alarm-- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Shared Objects (Dynamic loading)
On Sun, Aug 27, 2006 at 09:41:39PM -0400, Jasbinder Bali wrote: The actual scenario is like my perl code is on one computer and database server is on the other computer. The perl code needs to connect to that database server residing on a diff computer. I think client machine should also have DBI module in it. right? Right. The client machine needs DBI (the database-independent module), DBD::Pg (the PostgreSQL-specific driver), and libpq (the PostgreSQL client library). Also, how much of a change would it be if i have to migrate my triggers and functions written for C to Perl That depends on how many triggers you have, how elaborate they are, and how proficient you are at Perl. I tend to use PL/pgSQL for functions that involve a lot of SQL statements; I use PL/Perl or PL/Ruby for things like text manipulation that those languages are good at. -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Perl language creation failed
Hi,I'm trying the followingCREATE LANGUAGE plperlbefore executing my functions written in perlbut get the follwing errorERROR: could not access file $libdir/plperl: No such file or directory Can anyone tell me how to fix this?Thanks~Harpreet
Re: [GENERAL] Shared Objects (Dynamic loading)
Just wondering why would i need libpq here.Doesn't DBD::pg has its own functions for database related activities.I think i'm quite naive in this.Also, the triggers that i wrote in C are not all that elaborative. They are pretty basic triggers. Also, I'm a rookie in perl but don't need to do something hifi with it. Thanks,~JasOn 8/28/06, Michael Fuhr [EMAIL PROTECTED] wrote: On Sun, Aug 27, 2006 at 09:41:39PM -0400, Jasbinder Bali wrote: The actual scenario is like my perl code is on one computer and database server is on the other computer. The perl code needs to connect to that database server residing on a diff computer. I think client machine should also have DBI module in it. right?Right.The client machine needs DBI (the database-independentmodule), DBD::Pg (the PostgreSQL-specific driver), and libpq (the PostgreSQL client library). Also, how much of a change would it be if i have to migrate my triggers and functions written for C to PerlThat depends on how many triggers you have, how elaborate they are, and how proficient you are at Perl.I tend to use PL/pgSQL forfunctions that involve a lot of SQL statements; I use PL/Perl orPL/Ruby for things like text manipulation that those languages aregood at. --Michael Fuhr
Re: [GENERAL] Perl language creation failed
am Mon, dem 28.08.2006, um 1:25:51 -0400 mailte Harpreet Dhaliwal folgendes: Hi, I'm trying the following CREATE LANGUAGE plperl before executing my functions written in perl but get the follwing error ERROR: could not access file $libdir/plperl: No such file or directory Can anyone tell me how to fix this? You need the postgresql-plperl-8.1 (I assume, you are a Debian-User with PG 8.1). Install this package first and then CREATE LANGUAGE. HTH, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org