Re: [GENERAL] GROUP BY or alternative means to group
Thanks that is a help. I would be nice if any key could be used as those are normally the things I would do group by's Regards Mike Gould From my Samsung Android tablet on T-Mobile. The first nationwide 4G networkBruno Wolff III br...@wolff.to wrote:On Mon, Mar 12, 2012 at 16:18:05 -0400, Michael Gould mgo...@isstrucksoftware.net wrote: You need to include all columns that are not aggregrative columns in the group by. Even though that is the standard it is a pain to list all columns even if you don't need them In later versions of postgres this is relaxed a bit. If you are grouping by a primary key, you don't need to group by columns that are fixed by that key. For example the following query is accepted in 9.1 as gameid is a key for games and hence we don't need to also group by ga,es.title. SELECT games.gameid, games.title FROM games, crate WHERE games.gameid = crate.gameid AND games.contact = 'BOB' AND crate.touched = current_timestamp + '4 year ago' GROUP BY games.gameid HAVING count(1) 30 ORDER BY games.gameid ;
Re: [GENERAL] Counting different strings (OK%, FB%) in same table, grouped by week number
Sent from Samsung mobile Adrian Klaver adrian.kla...@gmail.com wrote: On 02/22/2012 12:36 PM, Alexander Farber wrote: Hello, I have a table holding week numbers (as strings) and user ids starting with OK, VK, FB, GG, MR, DE (coming through diff. soc. networks to my site): afarber@www:~ psql psql (8.4.9) Type help for help. pref= select * from pref_money; id| money | yw -++- OK19644992852 | 8 | 2010-44 OK21807961329 |114 | 2010-44 FB1845091917|774 | 2010-44 OK172682607383 |-34 | 2010-44 VK14831014 | 14 | 2010-44 VK91770810 | 2368 | 2010-44 DE8341 |795 | 2010-44 VK99736508 | 97 | 2010-44 I'm trying to count those different users. For one type of users (here Facebook) it's easy: pref= select yw, count(*) from pref_money where id like 'FB%' group by yw order by yw desc; yw| count -+--- 2012-08 |32 2012-07 |32 2012-06 |37 2012-05 |46 2012-04 |41 But if I want to have a table displaying all users (a column for FB%, a column for OK%, etc.) - then I either have to perform a lot of copy-paste and vim-editing or maybe someone can give me an advice? I've reread the having-doc at http://www.postgresql.org/docs/8.4/static/tutorial-agg.html and still can't figure it out... How about?: test= \d storage_test Table public.storage_test Column | Type | Modifiers -+---+--- fld_1 | character varying | fld_2 | character varying(10) | fld_3 | character(5) | fld_int | integer test= SELECT * from storage_test ; fld_1 | fld_2 | fld_3 | fld_int ---+---+---+- FB001 | one | | 4 FB002 | three | | 10 OK001 | three | | 5 OK002 | two | | 6 VK001 | one | | 9 VK002 | four | | 2 test= SELECT substring(fld_1 from 1 for 2) as id_tag,fld_2, count(*) from storage_test group by substring(fld_1 from 1 for 2),fld_2; id_tag | fld_2 | count +---+--- VK | four | 1 VK | one | 1 FB | one | 1 FB | three | 1 OK | two | 1 OK | three | 1 Thank you Alex -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] Question on Rules
Thanks I will change to a trigger Best Regards Mike Gould Sent from Samsung mobile David Johnston pol...@yahoo.com wrote: -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of mgo...@isstrucksoftware.net Sent: Saturday, February 18, 2012 5:17 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Question on Rules I am creating a rule which will copy a record when it is changed to a audittable. My question is that the first column is a UUID data type with a defined as auditaccessorid uuid DEFAULT isscontrib.uuid_generate_v4() NOT NULL, Right now I've got that set to NULL to allow the parser to compile. What value should I have in here since I want a newly created UUID? CREATE RULE log_accessor AS ON UPDATE TO iss.accessor WHERE NEW.* OLD.* DO INSERT INTO iss.auditaccessor VALUES (NULL, 'C', new.loaddtlid, new.seqno, new.billable, new.payind, new.code, new.description, new.ref, new.tractororcarrierflag, new.tractororcarrierno, new.tractorpct, new.charge, new.type, new.checkdate, new.checkno, new.processed, new.itemflag, new.tractortermloc, new.cost, new.batchno, new.editdatetime, new.edituser); Best Regards, Michael Gould Intermodal Software Solutions, LLC 904-226-0978 INSERT INTO table (serial_col1) VALUES (DEFAULT); Also, I presume you have a good reason for using a RULE instead of a TRIGGER? If not you should default to a TRIGGER for this kind of behavior. David J. -- 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] version controlling postgresql code
We use svn for all our version control at the moment Sent from Samsung mobile akp geek akpg...@gmail.com wrote: Hi all - Would like to know if any one of you have used CVS or some other version controlling tools to version the postgres code? Any recommendations? Appreciate your help Regards -- 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] GROUP BY or alternative means to group
You need to include all columns that are not aggregrative columns in the group by. Even though that is the standard it is a pain to list all columns even if you don't need them Best Regards Michael Gould Sent from Samsung mobile Alexander Reichstadt l...@mac.com wrote: Hi, the following statement worked on mysql but gives me an error on postgres: column addresses.address1 must appear in the GROUP BY clause or be used in an aggregate function I guess I am doing something wrong. I read the web answers, but none of them seem to meet my needs: SELECT companies.id,companies.name,companies.organizationkind,addresses.address1,addresses.address2,addresses.city,addresses.zip FROM companies JOIN addresses_reference ON companies.id=addresses_reference.refid_companies LEFT JOIN addresses ON addresses_reference.refid_addresses=addresses.id GROUP BY companies.id; What I did now was create a view based on above statement but without grouping. This returns a list with non-distinct values for all companies that have more than one address, which is correct. But in some cases I only need one address and the problem is that I cannot use distinct. I wanted to have some way to display a companies list that only gives me the first stored addresses related, and disregard any further addresses. Is there any way to do this? Thanks Alex -- 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] what Linux to run
Thanks to all Sent from Samsung mobile Chris Angelico ros...@gmail.com wrote: On Wed, Feb 29, 2012 at 3:58 PM, Scott Marlowe scott.marl...@gmail.com wrote: Note that Ubuntu also comes in a GUI free server edition as well. I can definitely state that Ubuntu 10.04 LTS Server edition is rock solid stable for the hardware I've run it on (48 core AMD and 40 core Intel machines with LSI, Arecam and 3Ware cards) Ubuntu 9.10 isn't LTS, but it's served me just fine. I have a server that's not been rebooted since July 2010 (including a database-using application process that has been running since boot, and is in constant use), and I don't feel like bringing it down to bring it up to date! Really, any of the main-stream Linuxes should be fine. Chris Angelico -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] problems installing odbc and .Net drivers
I am getting the following error when I run the install from stackbuilder.
Error trying to install file destination ${installdir} resolves to empty
value.
Does anyone know what might be causing this and how I can fix it.
This is the developer's mailing list. You should ask this question on
Best Regards
Michael Gould
[GENERAL] How do I manually delete the odbc, oledb and .net drivers
I'm having a issue installing or should I say uninstalling these 3 drivers. I have both 8.4.2 and 9.1 b2 installed. When I brought up the stack building to install these three drivers they say they are already installed. Well the drivers don't show up in the unistall a program window and the ODBC driver doesn't show up administrative console data sources. I've checked through my registry and there is no mention of these drivers in there. Not entries at least. It looks like I'm caught between a rock and a hard place. I backed up my db under 8.4.2 and then uninstalled. It said all components were sucessfully uninstalled. I did the same thing to 9.1. When I went back to install 8.4.2 first, when I came to the stack builder it showed all three of the drivers installed. It looks like I've got something out of sync here and I will probably have to manually clean this up, but how. Best Regards Michael Gould
Re: [GENERAL] Windows x64 : How do I get OSSP-UUID.sql contrib for postgresql x64
Hiroshi, I've what I believe your saying is the one that is in the contrib directory now should work fine on Window 64 Hiroshi Saito hiro...@winpg.jp wrote: Hi Grace-san. It is the same as windows. http://winpg.jp/~saito/pg_work/OSSP_win32/ 1. Deployment of tar.gz 2. apply the patch 3. configure and build by Linux or MinGW 4. config.h and win32.mak are set at the head of a source tree. 5. nmake -f win32.mak It can be used by 32 bits and 64 bits. Regards, Hiroshi Saito (2011/06/30 22:52), Grace Batumbya wrote: Hey Hiroshi, Do you have a make file to compile for x64 windows? May be if that is made available then ossp-uuid for x64 versions will become standard. *Grace Batumbya* Research Assistant | Seneca CDOT Phone: 416-491-5050 x3548 cdot.senecac.on.ca http://cdot.senecac.on.ca/ On 6/29/2011 1:02 PM, Hiroshi Saito wrote: Hi. here is an excuse... http://archives.postgresql.org/pgsql-general/2011-06/msg00738.php Regard, Hiroshi Saito (2011/06/30 1:50), Grace Batumbya wrote: Thanks Hiroshi, that solved the problem. If you do not mind, how did you go about to build ossp-uuid? *Grace Batumbya* Research Assistant | Seneca CDOT Phone: 416-491-5050 x3548 cdot.senecac.on.ca http://cdot.senecac.on.ca/ On 6/29/2011 12:38 PM, Hiroshi Saito wrote: Hi Grace-san. Is this helpful to you? http://winpg.jp/~saito/pg_work/OSSP_win32/build-x86-64/ Regards, Hiroshi Saito (2011/06/30 1:14), Grace Batumbya wrote: The installer for windows for 64bit versions of postgresql doesn't include ossp-uuid.sql. Does anyone know where or how to get this? Thanks -- *Grace Batumbya* Research Assistant | Seneca CDOT Phone: 416-491-5050 x3548 cdot.senecac.on.ca http://cdot.senecac.on.ca/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- 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] Windows x64 : How do I get OSSP-UUID.sql contrib for postgresql x64
Grace the ossp-uuid libraries have no make system to create a 64 bit version and I guess there are some technical reasons with the compiler. You can run them on Linux 64 bit and Windows 32 bit only. This is the reason I'd like to see native UUID support built in to the datatype. Best Regards Michael Gould Grace Batumbya grace.batum...@senecac.on.ca wrote: The installer for windows for 64bit versions of postgresql doesn't include ossp-uuid.sql. Does anyone know where or how to get this? Thanks -- Grace Batumbya Research Assistant | Seneca CDOT Phone: 416-491-5050 x3548 a href=http://cdot.senecac.on.ca/; target=_blankcdot.senecac.on.ca/a -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax
Re: [GENERAL] Windows x64 : How do I get OSSP-UUID.sql contrib for postgresql x64
Thanks... I didn't know that this had been done. Will be downloading shortly, thanks Mike Gould Hiroshi Saito hiro...@winpg.jp wrote: Hi Grace-san. Is this helpful to you? http://winpg.jp/~saito/pg_work/OSSP_win32/build-x86-64/ Regards, Hiroshi Saito (2011/06/30 1:14), Grace Batumbya wrote: The installer for windows for 64bit versions of postgresql doesn't include ossp-uuid.sql. Does anyone know where or how to get this? Thanks -- *Grace Batumbya* Research Assistant | Seneca CDOT Phone: 416-491-5050 x3548 cdot.senecac.on.ca http://cdot.senecac.on.ca/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] Gist Index: Problem getting data in GiST Support Functions penalty.
I am running 9.0.4 and I'm running it on Windows 7 Ultimate which is my development machine. I can't even create a server. When I try I get a message The server doesn't accept connections: the connection library reports could not connect to server: Connection refused (0x274D/10061) Is the server running on host 192.168.1.150 and accepting TCP/IP connections on port 5432? PostGres has been given access through the firewall on port 5432. My pg_hba.config file has the following line # TYPE DATABASEUSERCIDR-ADDRESSMETHOD # IPv4 local connections: hostall all 0.0.0.0/0 trust I am just trying to get logged in at this point so I'm basically not trying to use any security. This is becoming a huge problem for me. I need to have the schema imported into the database by Thursday of this week when we are supposed to show off a new module in our product. We've not had any problems in other customer locations trying to get in. Any ideas? Best Regards Michael Gould -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] UUID-OSP contrib module
I remember a few months ago that someone said that the UUID-OSP contrib module did not work on Windows 64. Is this still a limitation? Best Regards Michael GouldIntermodal Software Solutions -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax
Re: [GENERAL] Service user account 'postgres' could not be created -- 8.4.2-1 and Active Directory on Windows08 R2
With R2, I had to install using the admin account. I got those errors before. It worked once I used a admin account. If that doesn't work you might want to try turning UAC off, do the install and then turn back on. Best Regards Michael Gould Greg Corradini gregcorrad...@gmail.com wrote: Hello, I'm getting 'service user account 'postgres' could not be created' when using the postgresql-8.4.2-1 one-click installer (i also get the same error with a new version of postgresql 8.4.8-1) So i know in the past (version 8.3.x) there were problems with Windows Server 2008 R2 running Active Directory and postgresql around this same error. I'm running into exactly the same errors as described here: a target=_blank href=http://forums.enterprisedb.com/posts/list/1516.page;http://forums.enterprisedb.com/posts/list/1516.page/a After I get the error i cancel out of the installation, make sure there's no postgresql install, make sure there's no postgresql service running, blow away postgres user, make sure there is nothing in the registry and reboot. I try to reinstall again and run into the same problem. -- Greg -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax
[GENERAL] Protecting stored procedures
We want to protect the intellectual property of several of our stored procedures. If we put them in a separate schema in the database and only allow execute on anything in that schema would that protect our stored procedures? Best Regards -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax
Re: [GENERAL] Protecting stored procedures
We wouldn't make any of the system users a superuser in Postgres and in my 20+ years experience in the industry we provide software for, the possibility of having any users of the system that are able to hack or even understand what they have if they were able to is slim. I understand that anything can be reverse engineered. I had a relationship with a Russian program several years ago that could take the compile C modules, reverse engineer them to assembler and then back to the original C code to find and report bugs. That being said I don't worry too much about those types. Best Regards Michael Gould Andrew Sullivan a...@crankycanuck.ca wrote: On Thu, Apr 07, 2011 at 07:46:36AM -0500, Michael Gould wrote: We want to protect the intellectual property of several of our stored procedures. If we put them in a separate schema in the database and only allow execute on anything in that schema would that protect our stored procedures? If your customer can have superuser access on the machine (which effectively means, If your customer has control of the box,) then no, it won't. If you need to do this sort of thing, then you need to write the procedures in C and deliver only object code. Even that probably won't solve everything. A -- Andrew Sullivan a...@crankycanuck.ca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] Moving from SQL Anywhere to PostGres - First Time
Robert, We used Data Manager from JP to do this. Worked well. He recently added the ability to read OEM versions of ASA. How's come your moving away from SQL Anywhere? Best Regards Mike Gould Robert Paresi firstn...@lastname.net wrote: Hello, We have 700 user install base using Sybase SQL Anywhere 9.02 We are looking at migrating these installations over to PostGres 1. Very Very Short Answer Please - why should we? 2. Does anyone have a utility or migration application to read SQL Anywhere to go to PostGres 3. Does PostGres handle column descriptions (ie: you can give each column a 50 character description) and then access it via SQL Result Set (like I can do in Sybase) 4. Is there any Date/TimeStamp issues and conversions I need to know about. I use simply a DATE field and a TIME field - but do not use DATE/TIME stamp fields together. 5. What UI/Developer tools (GUI) are available to manage the database as well as add/change columns rather than doing it via SQL commands. Thank you. -Robert -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- 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 forums ... take 2
Personally I don't care what kind of forum interface is used. I just don't like the email because while I like to follow the forum, I spend a lot of time out of the office and I don't like to have to download all of that mail just to keep up. I'd much rather use something that I can access from my phone browser. I do this even with my other company email because I don't want to use up the space on my phone. Best Regards Michael Gould What I'm more interested in is still a word from the people who would actually *use* a forum on how this would be better than sites like Nabble and Gmane. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] The first dedicated PostgreSQL forum
I would like to see this type of setup used. I use my cell phone for about 90% of my internet access during the day when I'm on the road. I've found in the past that forums usually have better search tools and they also will not fill up my email address. I find many things in these forums which are great to be able to save and search on but would like to get rid of the posts from my emails. I don't want to not have access to them because when I'm board I can start to read through threads that interest me. I just don't want to have to download the emails to do that. Best Regards Mike Gould Thom Brown t...@linux.com wrote: On 13 November 2010 19:44, Thom Brown a href=mailto:t...@linux.com;t...@linux.com/a wrote: blockquote style= class=gmail_quote On 13 November 2010 19:38, Joe Conway a target=_blank href=mailto:m...@joeconway.com;m...@joeconway.com/a wrote: blockquote style= class=gmail_quote On 11/13/2010 11:24 AM, Tom Lane wrote: Thom Brown a target=_blank href=mailto:t...@linux.com;t...@linux.com/a writes: It's a shame that a forum can't act as a front-end for a mailing list, so signing up to the forum actually signs you up to a mailing list (if you're not already signed up), but without receiving any emails. Messages posted to the forum would get sent to the list, and vice versa. That would be cool. Wonder if there's something like that out there already. It exists -- not sure what they are using, but for example the Users forum here -- a target=_blank href=http://forum.sipfoundry.org/index.php;http://forum.sipfoundry.org/index.php/a -- is bidirectionally linked with a target=_blank href=mailto:sipx-us...@list.sipfoundry.org;sipx-us...@list.sipfoundry.org/a FUDForum by the look of it: a target=_blank href=http://cvs.prohost.org/index.php;http://cvs.prohost.org/index.php/a And mailing list integration looks pretty straightforward and well supported: a href=http://cvs.prohost.org/index.php/Mailing_List_Manager;http://cvs.prohost.org/index.php/Mailing_List_Manager/a Feasible? Desirable? -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935
[GENERAL] Linux
I know that this is probably a religion issue but we are looking to move Postgres to a Linux server. We currently have a Windows 2008 R2 active directory and all of the other servers are virtualized via VMWare ESXi. One of the reasons is that we want to use a 64 bit Postgres server and the UUID processing contrib module does not provide a 64 bit version for Windows. I would also assume that the database when properly tuned will probably run faster in a *inx environment. What and why should I look at certain distributions? It appears from what I read, Ubanta is a good desktop but not a server. Best Regards -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax
Re: [GENERAL] Linux
Whilst I won't discourage you from a move to Linux, which I think is a good idea in general (and personally, my choice is RHEL - or CentOS if you want free - for a production server), I will note that Hiroshi Saito has ported ossp-uuid to Win64 now, and we're working on getting it included in the next update of PG 9.0. That is good news, but I'm still thinking of moving to Linux because it appears that much more tuning can be accomplished and that you don't get the kitchen sink when you don't need it. Best Regard -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- 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] Linux
Thanks for all of the information. I will now need to spend some time looking at the various distributions that were mentioned here. Best Regards -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- 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] Missing uuid_generate_v1()
Is there a patch being worked on which will allow this contrib module to work properly under Windows 64 bit machines using the 64 bit Postgres server? We need to be able to support both Windows and Linux servers running Postgres. Best Regards Michael Gould Mike Christensen m...@kitchenpc.com wrote: Oh, BTW, Tom - You were right about symlinking.. What I did totally hosed Apache (though it didn't crash until 2 days later, then wouldn't restart).. Apache then griped about libuuid not loading. I had to get my friend Brian (Linux guru) to SSH in and clean up the whole mess I made.. I think now it's legit now.. On Fri, Oct 22, 2010 at 7:00 PM, Alexia Lau a...@esri.com wrote: Does anyone know where I can see what?s already fixed at 9.0.2? Thanks, Alexia On 2010-10-07 09:54, Dave Page wrote: On Thu, Oct 7, 2010 at 3:56 AM, Tom Lane t...@??? wrote: Mike Christensen m...@??? writes: On Wed, Oct 6, 2010 at 7:38 PM, Tom Lane t...@??? wrote: If you have a libossp-uuid.so.16, you might try symlinking libuuid.so.16 to that instead of carrying a separate file. So now what you're saying is if it's not broke, fix it till it is :) Well, it's hard to argue with that position ;-). But I'll try anyway: the platform-provided version of the library will be updated for bug fixes, compatibility rebuilds, etc. Your private copy won't be, unless you remember to do it. Eventually that's gonna bite ya. Of course the best fix would be for EDB to ship a build of Postgres that actually follows the platform-standard naming convention for this library. I'm still wondering why they're linking to libuuid.so. Dave? Because that's what comes with ossp-uuid 1.6.2, and I assume is what configure chooses when we use --with-ossp-uuid: [buildf...@bf2-linux ~]$ uuid-config --libs -luuid FYI, there was also a bug in the installer which didn't copy the library properly, which has been fixed for 9.0.2. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Problem installing 9.0.1 on Windows 7 x64
I'm trying to install 9.0.1 32 bit on Windows 7 x64. I cannot use the 64 bit server because we use the UUID contrib module and it evidently isn't compatible with Windows 64 bit libraries. When I try and install the 32 bit server I get the following error message. Problem running the post-install step. Installation may not complete correctly. The database initialisation failed. I've tried this using my normal account which has administrator rights and the system administrator account. Does anyone have any idea on why I can't install this? Best Regards Michael Gould -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax
Re: [GENERAL] Problem installing 9.0.1 on Windows 7 x64
It does appear so.. although I'm logged into the network administrator account. Weird but at least I can now track it down. Best Regards Mike Gould Dave Page dp...@pgadmin.org wrote: [CC'ing the list to close the loop] On Tue, Oct 5, 2010 at 7:43 PM, Michael Gould mgo...@intermodalsoftwaresolutions.net wrote: Dave, I found the problem. For some reason icacls.exe can't be found, in fact none of the executable files in system32 can be found from the c:\. I'm not sure what's up because when I look at my path statement it shows c:\windows\system32 when I do either a path or set from the command line run inside CMD.exe. At least I know that it's not a Postgres issue now. Very odd. Permissions perhaps? -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Performance statistics
Without asking for any blood letting, I'm wondering if there are any hard statistics available to prove if Windows Server is faster than, slower than or the same as for performance to one of the various Linux distributions. While our application is a commerical application, in our survey we've been asked for information on running the server on a Linux box vs a Windows Server. I suspect that running on a Linux server will be faster, however I'm concerned about maintenance at customer sites who have no Linux support and are a Windows based shop. The application is written with a Windows based language. We will also be hosting smaller companies so I want to see the statistics and if Linux is a clear winner, the best distrubution to work with. Best Regards -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax
[GENERAL] Scratching my head why results are different between machines.
I have several tables in a SQL Anywhere 10 database that I'm converting to PostgreSQL 8.4.2. In ASA we did not use any GUID columns but we are in Postgres. I have one table that is citystateinfo and the primary key is a UUID and it is automatically generated using the contrib module oosp-uuid. That works fine. We are also using the citext contrib module on most of our text columns so that we can do case insensitive searches. We now have this working properly. Here is a simple select that is used to get the UUID primary key for the citystateinfo table to put into a terminaladdress table. select citystateinfoid as KeyName from iss.citystateinfo where cityname='JACKSONVILLE' and statecode='FL' and zipcode='32226'. In PGAdmin this returns the correct uuid in the proper format. In the program that we're using to convert this data (a commerical product called DMC), when it is run on the tech support machine at the authors company, it returns the proper UUID to all columns where there is a matching row in the citystateinfo table. On my machine the UUID that is returned is 16 bytes and I cannot make out any relevant numbers from the UUID key in the citystateinfo table. I've tried this in a Windows XP machine and a Windows 7 64 bit. Now here is the weird thing. I did a complete backup of my database and had the author restore the data on his. It works every time there. He then did a pg_dumpall and I reloaded my db with his script and while his runs all of the queries fine, mine is still not returning the proper length UUID column. I'm using the UTF ODBC driver 8.4 and Postgres 8.4.2. Does anyone have any idea on what could possibly be going on? It's running find on computers located in other locations but doesn't work properly on either one of my machines. It seems to me that it must be enviornmental. The author has both Windows 2008 server and Windows 7 64 bit. Best Regards Michael Gould
Re: [GENERAL] Scratching my head why results are different between machines.
Justin Graf jus...@magwerks.com wrote: On 3/3/2010 3:40 PM, Michael Gould wrote: On my machine the UUID that is returned is 16 bytes and I cannot make out any relevant numbers from the UUID key in the citystateinfo table. I've tried this in a Windows XP machine and a Windows 7 64 bit. Now here is the weird thing. I did a complete backup of my database and had the author restore the data on his. It works every time there. He then did a pg_dumpall and I reloaded my db with his script and while his runs all of the queries fine, mine is still not returning the proper length UUID column. I'm using the UTF ODBC driver 8.4 and Postgres 8.4.2. You state the PgAdmin returns the correct result on your machine connecting to the database that returns the wrong result using Data Conversion App, RIGHT??? Any SQL client that can accept and process a query will bring it back correctly. Also the author of DMC's can run it on his local PostGres server restored using my backup from this afternoon and he gets the correct results through DMC. On my machine I get a UUID that looks like 8BA92F06-BCD6-49. Also I've noticed that when I bring up the ODBC administrator, I do not see any entry for the Postgres ODBC driver. I've tried to uninstall the driver, reboot and reinstall with the same results. Best Regards Mike Gould -- 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] Scratching my head why results are different between machines.
One thing I've noticed is that on my machines, when I install the odbc driver I get no error messages but when I look in the ODBC administrator I do not see any entry for PostGres in the drivers list. I do know that it somehow is working because the DMC conversion tool only uses ODBC and builds it's data structures based on what it gets back from querying the database. Programs like WinSQL also work and I believe it uses ODBC. In the past I believe I remember seeing the Postgres driver listed in the ODBC admin tool under the drivers tab. I'm running on a Windows 7 64 bit machine and I'm logged into our domain as the domain administrator. Is there something else I need to do to install the odbc driver under windows? I've even turned UAC off and it didn't seem to help Best Regards -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- 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] Scratching my head why results are different between machines.
Is there anyone out there using Windows 7 64 bit with Postgres 8.4.2 and the psqlodbc driver? I've tried to install using the one that is available with the standard windows installer. I've also tried to install with the one from EDB. It appears that the installer finishes the install but when I look for the drivers there are none in the odbc administrator. I've installed and uninstalled a couple of times. Does anyone have any idea on what is going on here and how to fix the problem? I've logged in as the domain adminstrator and the system administrator for our domain and I've turned UAC off also, but as of yet no luck. I know that they are at least partially installed because I can access our database via two tools that use ODBC as it's access method. It's just that they are not showing up in the administrator and this makes me think that I've got a problem. I'm trying to get a data conversion tool to work (was working when I was using Windows XP) to convert data from SQL Anywhere into PostgreSQL 8.4.2. The problem is with UUID columns that are looked up and used in other tables. The UUID's are created properly in their parent tables, but the value isn't correct when it is used as a lookup column. The author of this product has taken a complete backup of my database and restored it on his machine. When he runs the conversion routine it produces the correct data. The only difference between his machine and mine seems to be how the odbc driver is installed. He's using the one he downloaded from EDB. He sent it to me, I uninstalled the previous one I had, rebooted and installed the one he sent me. Same results, no driver shows up in the odbc admin. Best Regards Michael Gould Justin Graf jus...@magwerks.com wrote: On 3/3/2010 5:16 PM, Michael Gould wrote: One thing I've noticed is that on my machines, when I install the odbc driver I get no error messages but when I look in the ODBC administrator I do not see any entry for PostGres in the drivers list. I do know that it somehow is working because the DMC conversion tool only uses ODBC and builds it's data structures based on what it gets back from querying the database. Programs like WinSQL also work and I believe it uses ODBC. In the past I believe I remember seeing the Postgres driver listed in the ODBC admin tool under the drivers tab. I'm running on a Windows 7 64 bit machine and I'm logged into our domain as the domain administrator. Is there something else I need to do to install the odbc driver under windows? I've even turned UAC off and it didn't seem to help /pre Can't help on the Windows 7 ODBC not appearing in the drivers list If you can manage to configure a Data Source Name turn on the logging then connect using this DSN in your DMC app just do a simple select The log will get big quick so keep the selects small and use limit. This will allow to see what ODBC driver is up to open the C:\mylog_XXX and look at and you will find all the commands sent the Postgresql and the results then you will look for something like this. This will tell us what the Select looks like and what being sent DMC app. b[1444-387.642]Exec_with_parameters_resolved: copying statement params: trans_status=1, len=51, stmt='Select user_password from mediawiki.mwuser limit 10'/b [1444-387.645] stmt_with_params = 'Select user_password from mediawiki.mwuser limit 10' [1444-387.646]about to begin SC_execute [1444-387.647] Sending SELECT statement on stmt=02C5D8C0, cursor_name='SQL_CUR02C5D8C0' qflag=0,1 [1444-387.648]CC_send_query: conn=04313E00, query='Select user_password from mediawiki.mwuser limit 10' --snip-- b[1444-387.665]qresult: len=44, buffer=':B:e2e28556:8ff13b68ebc64eccad0921d1571bf08c'/b [1444-387.666]qresult: len=0, buffer='' b[1444-387.667]qresult: len=44, buffer=':B:60a118ba:592cb633245cc330d48cc2e52c922f90'/b [1444-387.667]qresult: len=0, buffer='' [1444-387.668]qresult: len=0, buffer='' [1444-387.668]qresult: len=0, buffer='' [1444-387.669]qresult: len=0, buffer='' [1444-387.669]qresult: len=0, buffer='' [1444-387.670]qresult: len=0, buffer='' All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored. CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately. Thank
Re: [GENERAL] Roles with passwords; SET ROLE ... WITH PASSWORD ?
One other topic that is related to this is that we now have a expire date but it would be nice to have a number of days also. This would make it easy to force the user to change their passwords every X days if internal security is being used instead of something like Kerberos or LDAP. Best Regards Michael Gould Tom Lane t...@sss.pgh.pa.us wrote: Craig Ringer cr...@postnewspapers.com.au writes: Anyway ... I'm curious about whether `SET ROLE rolename WITH PASSWORD' is something that's technically practical to implement in PostgreSQL and what people think about the idea. Seems like it would have all the standard problems with cleartext passwords being exposed in pg_stat_activity, system logs, etc. Also, what about people who are using more-secure-than-password auth methods, like Kerberos? I'm not really for it. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Public and Grants
I have a database with a schema called ISS. This is where all of our application defintions are stored. We did add 2 contribute modules (citext) and guid generator and both of these by default went to the public schema. It is our intent to not allow any access to public by our users. A few questions 1. Can I reinstall the contrib modules in the ISS schema only or do they need to be in the public schema 2. If they need to stay in the public schema and I don't want to give any insert, update, delete or select access to public, can I revoke those privileges and just give execute on the functions that were added by the contrib module. 3. If I can reinstall the contrib modules in the application schema, can I delete the public schema or does it still need to be there and I would just revoke all except for the superuser id which would be for our installer or tech support if needed. We have a separate userid for the security administrator. All of the functions that the security administrator needs are provided by a application module and they will not be directly accessing the database via a SQL utility at all. Best Regards -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Defining roles
In our system we have a hybrid security system. We have tables that I want to make sure that based on membership in a group that update and/or delete is not allowed to a specific group. We also have application level security which is much more granular and is much more job function based. Our application is a transportation application, so a user might have insert, update and delete in order entry but a dispatcher would not have the ability to delete a order, they must cancel it with a reason code. These would both be part of the same ROLE in the database. Trying to maintain the database to match the application security would become cumbersome for our customers. Now for the question, if I specifically revoke a update or delete on a per table basis for a role to I still have to specifically define what security attributes they have access on? If this doesn't work, would I give access to the schema and then just specifically revoke the update or delete functionality for just the tables I'm looking to protect. Best Regards Mike Gould -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] DDL with Reference on them.
I have a question about using Reference. I have several tables that are
defined such as below
CREATE TABLE iss.accessor (
loaddtlid UUID NOT NULL ,
seqno SMALLINT NOT NULL ,
billable VARCHAR(1) DEFAULT 'N' CHECK(billable IN ('N','Y')) NOT NULL ,
payind VARCHAR(1) DEFAULT 'P' CHECK(payind IN ('P','F')) NOT NULL ,
code UUID REFERENCE accessorcodes (code) ,
description CITEXT ,
ref CITEXT ,
tractororcarrierflag VARCHAR(1) DEFAULT 'T' CHECK(tractororcarrierflag IN
('T','C')) NOT NULL ,
tractororcarriernoid UUID ,
tractorpct DECIMAL(6,4) DEFAULT 0 CHECK(tractorpct BETWEEN 0 AND 1) NOT
NULL ,
charge DECIMAL(7,2) DEFAULT 0 ,
type VARCHAR(1) DEFAULT 'N' CHECK(type IN ('N','V','D','R','A','S')) NOT
NULL ,
checkdate DATE ,
checkno CITEXT ,
processed VARCHAR(1) DEFAULT 'N' CHECK(processed IN ('N','Y')) NOT NULL ,
itemflag VARCHAR(1) DEFAULT 'N' CHECK(itemflag IN ('N','Y')) NOT NULL ,
tractorterminalid UUID REFERENCES terminal (terminalid) ,
cost DECIMAL(7,2) DEFAULT 0 ,
createdatetime TIMESTAMP ,
createuser CITEXT ,
editdatetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ,
edituser CITEXT DEFAULT CURRENT_USER
)
As you can see there are a few columns which reference back to another
table. What I need to know is how does Postgres work with these columns.
Can I insert or update a row if those columns are null or are they required
to have a non-null value in order for the row to be inserted or saved?
Best Regards
Michael Gould
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] LDAP using Active Directory
I am wondering how others handle the login situation. We use Active Directory and require our users to change their passwords every 30 days. Currently in our old system using SQL Anywhere we use the integrated login feature. Our db server is Windows 2003 R2 I believe we can mimic this in Postgres. What are peoples feelings about using passwords in Postgres in this situation? We know that only people authenticated to access our servers are actually getting logged in. All of our users must login through Citrix and access our system via our Citrix web page login. We I do not believe we can capture the password from Active Directory that the user types so I really do not want to use a password on the Postgres side. We do have application level security also which only allows certain users (same as the login id) access to the allowed area's within the system and only at the level of access prescribed within the system. What are others thoughts on this. With SQL Anywhere if you are using integrated logins, you need to enter a password when the account is first defined to the database but it is bypassed from that point forward unless you remove their access to use integrated logins. Best Regards -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Partition tables
I am considering whether or not to partition a few tables in our system. The guide lines I read said that this could help when the size of a table exceeds the amount of system memory. I'm wondering if this statement should be table or database. The way our system operates is that each individual location is actually a separate operation owned by another party. In most cases the data from one location should not be seen by others. There are several tables which are global in nature which hold mainly lookup information, but the actual processing tables are by location. I am wondering if partitioning would be a good way to isolate the information for each location. Each database would be created by location number. Out db servers is a dual quad Intel with 4 Gigs of RAM running Windows 2000 Enterprise Server. All Client machines are running Quad core servers with 8-16 gig of RAM partitioned using Windows 2003 and accessed by Citrix. The total size of our database with 5 years worth of data is about 3.4 gig. In the business we are in, we open about 5-7 new locations each year and close 2-3. I was also thinking that if each partition was by location it would be easier to disconnect the partion tables to use for historial use when we close a location. We probably would get 10 or so queries on the closed locations 6 months after closing. Does this seem like an appropriate use of table partitioning? Best Regards -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Problem trying to load trigger
CREATE OR REPLACE FUNCTION iss.accessor_trigger() RETURNS trigger AS $$ BEGIN IF(TG_WHEN = 'BEFORE') THEN IF(TG_OP = 'INSERT') THEN NEW.createdatetime := current_timestamp NEW.createuser := current_user ELSIF (TG_OP = 'UPDATE') THEN INSERT into iss.auditaccessor SELECT 'B','C',OLD.*; ELSIF (TG_OP = 'DELETE') THEN INSERT into iss.auditaccessor SELECT 'B','D',OLD.*; END IF; RETURN NEW; ELSIF (TG_WHEN = 'AFTER') THEN IF(TG_OP = 'INSERT') THEN ELSIF (TG_OP = 'UPDATE') THEN INSERT into iss.auditaccessor SELECT 'A','C',NEW.*; ELSIF (TG_OP = 'DELETE') THEN END IF; RETURN OLD; END IF; END $$ LANGUAGE plpgsql VOLATILE; I'm trying to use a single trigger to do a couple of things... The first is when a record is created to timestamp the createdatetime and the createuser columns with the current date/time or user. If there is a update then I want to make before and after images of the record and if a delete I want to keep the before image of the record. when I try and load this I get the following errors. I'm new to postgres so I'm not sure where I'm off here. Any help is greatly appreciated ERROR: syntax error at or near $1 LINE 1: SELECT current_timestamp $1 := current_user ELSIF ( $2 =... ^ QUERY: SELECT current_timestamp $1 := current_user ELSIF ( $2 = 'UPDATE') THEN INSERT into iss.auditaccessor SELECT 'B','C', $3 .* CONTEXT: SQL statement in PL/PgSQL function accessor_trigger near line 8 ** Error ** ERROR: syntax error at or near $1 SQL state: 42601 Context: SQL statement in PL/PgSQL function accessor_trigger near line 8 -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- 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] Problem trying to load trigger
Thanks to everyone who answered. Getting used to PostGres's unique syntax can take time getting used to. Best Regards Michael Gould Tom Lane t...@sss.pgh.pa.us wrote: Michael Gould mgo...@intermodalsoftwaresolutions.net writes: CREATE OR REPLACE FUNCTION iss.accessor_trigger() RETURNS trigger AS $$ BEGIN IF(TG_WHEN = 'BEFORE') THEN IF(TG_OP = 'INSERT') THEN NEW.createdatetime := current_timestamp NEW.createuser := current_user You've forgotten to end these statements with semicolons ... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] 8.4.0 installer for Windows from EnterpriseDB does not seem to include pgagent
Dave, Are there any plans to add the plug-ins that were available in the 8.3 install to the stackbuilder component? Best Regards Michael Gould Dave Page dp...@pgadmin.org wrote: On Thu, Jul 23, 2009 at 8:06 AM, Magnus Hagandermag...@hagander.net wrote: On Thu, Jul 23, 2009 at 08:45, Steffen Kuhns.k...@evo-solutions.com wrote: Hallo Knut, this is right see following link for details about enterpriseDB installers and provided features http://www.enterprisedb.com/products/postgres_plus/overview.do#ui-tabs-6 8 That page refers to the Postgres Plus product. The community product packaged by enterprisedb is something different. I thought we had pgagent as a stackbuilder application, but it seems I was mistaken. Dave? pgAgent binaries can be found at http://www.postgresql.org/ftp/pgadmin3/release/pgagent/ There's no StackBuilder component for it. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] citext contrib module (building indexes)
I've got several columns in my database that need to have case insensitive searches done so I've loaded the citext control module and have changed the data types to citext. When I create a index on a column that is defined as a citext, how is that going to be treated. Will Postgres use the index to search or will it always do a table scan? Best Regards -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax
[GENERAL] uuid-ossp for PostGres 8.4 running on Windows
Does anyone have a install or the proper files (not the source) to install the uuid-ossp contrib files for 8.4? The one thing that I think that EnterpriseDB install is lacking in is the contrib modules that have been in the previous postgres version installation. Is this something that is going to be added back into the EnterpriseDB installer? At any rate my main concern now is to get the uuid-ossp install for 8.4 so that I can use 8.4 for our new development. Best Regards -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax
Re: [GENERAL] uuid-ossp for PostGres 8.4 running on Windows
Thanks, that worked perfectly. Best Regards Michael Gould Sachin Srivastava sachin.srivast...@enterprisedb.com wrote: Hello Michael, In the EnterpriseDB installer, uuid-ossp contrib module is available but not loaded/enabled by default. To enable uuid-ossp module simply run the uuid-ossp.sql file (share\contrib\uuid-ossp.sql). This is something that i did: psql.exe -U postgres -d postgres -f share\contrib\uuid-ossp.sql Then from psql i fired the below query and got results, /pre SELECT uuid_generate_v3(uuid_ns_url(), 'a href=http://www.postgresql.org;http://www.postgresql.org/a'); /pre Hope it helps. On 07/18/2009 08:00 PM, Michael Gould wrote: Does anyone have a install or the proper files (not the source) to install the uuid-ossp contrib files for 8.4? The one thing that I think that EnterpriseDB install is lacking in is the contrib modules that have been in the previous postgres version installation. Is this something that is going to be added back into the EnterpriseDB installer? At any rate my main concern now is to get the uuid-ossp install for 8.4 so that I can use 8.4 for our new development. Best Regards Michael Gould, Managing Partner Intermodal Software Solutions, LLC904.226.0978904.592.5250 fax -- Regards, Sachin Srivastava a href=http://www.enterprisedb.com;www.enterprisedb.com/a /pre
[GENERAL] Ascending / Descending Indexes
In some SQL engines the engine doesn't need to define both Ascending and Descending indexes on the same column. Does Postgres need to have indexes defined for both Ascending and Descending sorts? We use quite a few of these types of sorts. Best Regards -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax
[GENERAL] Custom Class variables
I have created the following in my postgres.conf file custom_variable_classes = 'iss' In a SQL session I've tried Set iss.one = '1' set iss.two = '2' Select * from iss; How do I access the values from the custom class in sql code? Best Regards Michael Gould -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Request for features
I would like to know what the formal method of requesting new features are for Postgres and how are decisions made which features are included in a future release or not. Best Regards -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Execute Immediate
In many SQL dialets there is the availability of Execute immediate which allows you to build global temp tables or views at run time and even stored procedures at runtime and then execute them inline. Is there a way to do this in PostGres 8.4 Best Regards -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax
Re: [GENERAL] Execute Immediate
Thanks, that is exactly what I need as a workaround to session variables. The temp tables will work as we need them as we only have a 5 that are temp tables and preserve is needed only for the active session, once the session has ended the temp table should go away. Pavel Stehule wrote: 2009/7/12 Michael Gould mgo...@intermodalsoftwaresolutions.net: It does look to me that PostGres supports temporary tables and using with the commit preserve rows appears to work similar to how global or local temp tables would work. The only thing I need to know (or test) is whether they are session or connection safe. temp tables in pg are related to session. After session end, the temp tables are automatically dropped. postgresql has not session variables - but you can emulate it http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Any_other_session_variables http://www.postgresql.org/docs/8.3/static/plperl-global.html regards Pavel Stehule Besides global or local temp tables, I would like to see a CREATE Variable which would allow a global variable to be created and used. It would allow the ability to set and retrieve values once instead of doing the select each time. These variables have a session life and must be set each time you login to the system. Best Regards Michael Gould Pavel Stehule wrote: Hello plpgsql has execute statement, that has similar behave like execute immediate in others systems. note - postgresql doesn't support global temp tables yet. regards Pavel Stehule 2009/7/12 Michael Gould mgo...@intermodalsoftwaresolutions.net: In many SQL dialets there is the availability of Execute immediate which allows you to build global temp tables or views at run time and even stored procedures at runtime and then execute them inline. Is there a way to do this in PostGres 8.4 Best Regards Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Client only install
We are running our PostGres db on Windows 2008 Server but have several clients who are running various other versions of Windows (XP, Vista, etc). Is there are binary install for just the client side install so that we do not have to install everything? If there isn't one available how do we run just the client side on the workstations? Do we just not start the postmaster on the client? Best Regards -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Added parameter for CREATE ROLE
It would be nice if during create role we could have a parameter to set the number of days that a password is valid instead of just a timestamp. Best Regards -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
