Re: [GENERAL] Fwd: How do I determine my data dir for a created database for pg_ctl?
Matthew Pettis wrote: I have a database I can psql into... How can I determine what its absolute path is so I can use pg_ctl on it to restart it? Because when I use pg_ctl, it tells me I have to provide it in the -D flag as an argument. show data_directory; klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] FW: syntax for Copy command via libpqxx
Hi, Can anyone tell me the proper syntax for using copy command using libpqxx .? the manual in pqxx.org doesnot provide the full tutorial .Can anybody tell me a proper link where I can find the full tutorial for libpqxx? Thanks in advance, Roshni
Re: [GENERAL] cache lookup failed
c k wrote: I got following error while testing some newly created functions. ERROR: cache lookup failed for function 111462 CONTEXT: PL/pgSQL function uf_postdoc line 25 at FOR over SELECT rows ** Error ** ERROR: cache lookup failed for function 111462 SQL state: XX000 Context: PL/pgSQL function uf_postdoc line 25 at FOR over SELECT rows what this means? and how to solve it? I searched manual but not got much help. Look there: http://www.postgresql.org/docs/current/static/plpgsql-statements.html Keywords: plan, cache You probably dropped the function and created a new function with the same name. Disconnect and reconnect, and everything should work. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Regarding access to a user
Dear Team First of all I would like to thank you to provide a spatial database fro GIS developers and user. But I have doubt in the usage. I have created a few databases (i.e. X, Y, Z) in a default Server and I also created a user (i.e. Us). Now what I want is to set privilege that whenever the user log in as Us he can only see database Y, none other than that. The user Us could have all rights for database Y but can't go for any other database (X or Z). I already tried for GRANT command but it doesn't seems to be fruitful as such. Please provide some assistance regarding the same as it is very required for the further proceeding of my work. Thanks Regards, Shashwat Nigammailto:[EMAIL PROTECTED] | Software Engineer | Geographical Information System Satyam Computer Services Limitedhttp://www.satyam.com/, Hyderabad | MOBILE: +919951467324 | DISCLAIMER: This email (including any attachments) is intended for the sole use of the intended recipient/s and may contain material that is CONFIDENTIAL AND PRIVATE COMPANY INFORMATION. Any review or reliance by others or copying or distribution or forwarding of any or all of the contents in this message is STRICTLY PROHIBITED. If you are not the intended recipient, please contact the sender by email and delete all copies; your cooperation in this regard is appreciated.
[GENERAL] Re: Re: pg_restore fails on Windows
Magnus Hagander wrote Tom Lane wrote: =?us-ascii?Q?Tom=20Tom?= [EMAIL PROTECTED] writes: Magnus Hagander wrote: Attached is a pg_restore.exe off CVS tip today, which should include the patch. Please try this one. I tested the restore using the provided pg_restore.exe. The output is: pg_restore: [archiver (db)] could not execute query: could not send data to server: No buffer space available (0x2747/10055) According to http://support.microsoft.com/kb/201213 this is an acknowledged bug that's been broken since Windows 95, so I suppose we should conclude that M$ is unwilling or incompetent to fix it. Yup, I was just reading that one when I saw your email. I finally got around to building a libpq with this change in it - attached here. Tom (not Lane), can you test this please? It shouldn't be this one really, since it doesn't list any modern Windows versions as having this issue, but it's worth a try. Tested. The restore comes through successfuly with the patched libpq. So I take it that it's caused by the MS issue. Again, we are using WinXP Professional SP2. Perhaps the system buffer space was _increased_ in XP (10MB comes through easily), still if the block is too large, it occurs (speculation). Since I don't know the implementation details of the patch I'd like to ask: 1.This is not official patch, didn't pass the review/test cycle; do you think that it can be used in the production environment (any side effects or so..)? If not, is the patch due for a next version? 2.Our production PG version is 8.1.3. For some reasons it is not possible to upgrade to the LATEST; I tested the libpq also on this version and it worked. Is it OK? I mean, did it worked by chance or the library API contracts didn't change between this version and latest? One other thing not asked here btw - is this over SSL or not? SSL is out of game, we are not using it. Possibly the best workaround is something like + #ifndef WIN32 sent = pqsecure_write(conn, ptr, len); + #else + /* Windows tends to fail on large sends, see KB 20213 */ + sent = pqsecure_write(conn, ptr, Min(len, 65536)); + #endif in pqSendSome(). The backend seems to not be subject to a similar problem because it's already filtering its output through a limited-size buffer. The other option would be to turn off buffering in the sockets per the third bulletpoint. But I think that's an even uglier solution. Regards, Tomas -- 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] pg_restore fails on Windows
Tom Tom wrote: Magnus Hagander wrote Tom Lane wrote: =?us-ascii?Q?Tom=20Tom?= [EMAIL PROTECTED] writes: Magnus Hagander wrote: Attached is a pg_restore.exe off CVS tip today, which should include the patch. Please try this one. I tested the restore using the provided pg_restore.exe. The output is: pg_restore: [archiver (db)] could not execute query: could not send data to server: No buffer space available (0x2747/10055) According to http://support.microsoft.com/kb/201213 this is an acknowledged bug that's been broken since Windows 95, so I suppose we should conclude that M$ is unwilling or incompetent to fix it. Yup, I was just reading that one when I saw your email. I finally got around to building a libpq with this change in it - attached here. Tom (not Lane), can you test this please? It shouldn't be this one really, since it doesn't list any modern Windows versions as having this issue, but it's worth a try. Tested. The restore comes through successfuly with the patched libpq. So I take it that it's caused by the MS issue. Again, we are using WinXP Professional SP2. Perhaps the system buffer space was _increased_ in XP (10MB comes through easily), still if the block is too large, it occurs (speculation). Yes, that sounds quite likely. They fixed the symptoms, but not the underlying problem. Since I don't know the implementation details of the patch I'd like to ask: 1.This is not official patch, didn't pass the review/test cycle; do you think that it can be used in the production environment (any side effects or so..)? If not, is the patch due for a next version? I plan to apply it to HEAD and supported back-branches (8.3 and 8.2) now that you have verified that it works, so it will be in the next versions. The only potential side-effect is that it will be slightly slower on packets 64kb, but I doubt that's even measurable in most cases. So yes, it should be safe to use in production. 2.Our production PG version is 8.1.3. For some reasons it is not possible to upgrade to the LATEST; I tested the libpq also on this version and it worked. Is it OK? I mean, did it worked by chance or the library API contracts didn't change between this version and latest? Note that libpq is only the *client* side. There is no patch necessary on the server. It might be easier to upgrade than the server? Did you test it with the pg_restore that I sent, or with the one from 8.1? The pg_restore I sent was for HEAD, as well as the libpq I sent, so you shouldn't use those in production long-term. For binaries, we don't provide backpatches for 8.1 any more (it's not a supported platform on Windows!), but you might be able to use the latest 8.2 libpq with the 8.1 pg_restore - you'll have to try that once the release is eventually out. Or you can just apply the patch to the latest 8.1 libpq and build it yourself, of course. I think it should apply just fine. //Magnus -- 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's size of your PostgreSQL Database?
Mark Roberts wrote: 1. 2.5-3TB, several others that are of fractional sisize. ... 5. They do pretty well, actually. Our aggregate fact tables regularly join to metadata tables and we have an average query return time of 10-30s. We do make some usage of denormalized mviews for chained/hierarchical metadata tables. Just out of curiosity, how do you replicate that amount of data? ... A few notes: our database really can be broken into a very typical ETL database: medium/high input (write) volume with low latency access required. I can provide a developer's view of what is necessary to keep a database of this size running, but I'm under no illusion that it's actually a large database. I'd go into more details, but I'd hate to be rambling. If anyone's actually interested about any specific parts, feel free to ask. :) I'd be very interested in a developers view of running and maintaining a database this size. Mostly what choices is made during development that might have been different on a smaller database. I'm also curious about the maintenance needed to keep a database this size healthy over time. Regards, /roppert Also, if you feel that we're doing something wrong, feel free to comment there too. :) -Mark -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Sequences using libpqxx
Hi all, Does the sequence commands like nextval().setval(),curval() exist using libpqxx? Thanks and regards Roshni
[GENERAL] SELECT query experts, anyone?
Hi Experts, is threre any way to SELECT values in columns instead of rows? For example select products and their manufacters in the followin way: product1; manufacturer1; manufacturer2;,,, manufacturerN product2; manufacturer3; product3; manufacturer1;.. manufacturerN-1 With a function you could put the product manufacturers in one string, but I would like to have them in columns. How about arrays. Is there a way to SELECT values in an array to columns? Best regards and thanks, Teemu Juntunen
Re: [GENERAL] WAL archiving to network drive
On Tue, 19 Aug 2008, Rob Adams wrote: Is it best to give the 'postgres' user network access archive the WAL files directly to the network drive? Or archive the WAL files to a local folder and then use a scheduled task to move them to the network drive? I normally write first to a local drive then move to the network, because that has some useful properties: 1) As as long as there's local disk space, the archive_command called always returns quickly. While performance here doesn't really matter it makes me feel better. 2) If there is a problem with the network drive, the slow/blocked network write is not happening where the database is waiting for it. 3) In an emergency where the usual network drive is unavailable, you can temporarily change your archive strategy without touching the database server's configuration. 4) The copying process doesn't need read access to the main database, and the database server process doesn't need permissions on the network drive. I find myself giving it a completely different set of permissions, so that it can't touch the database at all really, but can run alert scripts and take actions I'd never want the database user to have access to. The main downside of doing it this is way is you're stuck writing and debugging a lot more scripts to make sure everything happens correctly. I've found the alternative--just putting something into archive_command does the transfer directly--isn't quite flexible enough when things go wrong for me to be comfortable with it. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- 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.3.3 win32 crashing
Tom Lane wrote: Klint Gore [EMAIL PROTECTED] writes: [ heavy RAISE NOTICE traffic crashes the server ] Tom Lane wrote: Please try to narrow it down a little. client=warning, log=warning completes client=warning, log=notice fails client=notice, log=warning completes client=notice, log=notice fails It fails a heck of a lot quicker with client=warning, log=notice. Okay, so the problem is definitely on the postmaster-log side. Please show us all of your logging-related configuration settings. ISTR that Magnus was poking at some problem in the WIN32 log collector code, but whether this is related is not yet clear ... Maybe it's related to another bug reported earlier. See here for the complete references: http://archives.postgresql.org/message-id/20080625133912.GB17573%40alvh.no-ip.org -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] SELECT query experts, anyone?
Teemu Juntunen wrote: Hi Experts, is threre any way to SELECT values in columns instead of rows? For example select products and their manufacters in the followin way: product1; manufacturer1; manufacturer2;,,, manufacturerN product2; manufacturer3; product3; manufacturer1;.. manufacturerN-1 With a function you could put the product manufacturers in one string, but I would like to have them in columns. How about arrays. Is there a way to SELECT values in an array to columns? This should work: SELECT product,array(SELECT manufacturer FROM manufacturers WHERE manufacturer_id=products.manufacturer_id) WHERE ... or something along the lines of the above. Regards Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] [ADMIN] Regarding access to a user
Shashwat_Nigam [EMAIL PROTECTED] wrote: Now what I want is to set privilege that whenever the user log in as Us he can only see database Y, none other than that. The user Us could have all rights for database Y but can't go for any other database (X or Z). The first thing you need to do is configure host based authentication: http://www.postgresql.org/docs/8.3/interactive/auth-pg-hba-conf.html Among other things, this allows you to restrict which users can connect to which databases. -Kevin -- 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] Fwd: Restarting with pg_ctl, users, and passwords.
Matthew Pettis wrote: I've created a database and can log into it and do stuff with the tables using psql. However, I have a CGI app that wants to call the database (all on the same machine), but gets a 'FATAL: Ident authentication failed for user postgres' error. You *REALLY* shouldn't use the `postgres' user for applications or normal use. Create a new non-superuser, grant them the required priveleges, and use them for the web application. If you like you can make them the owner of a new database, so they can do basically whatever they want with that database but not mess with your others. See the documentation for CREATE USER, CREATE ROLE, GRANT, and CREATE DATABASE . now, while logged onto my Linux user account 'postgres', I can psql into my database without having to provide a password. It's using ident authentication on a UNIX socket connection. The database knows you are logged in on the UNIX account postgres (or have su'd to it) because the operating system can tell it that. So it trusts you. The web app will be using a TCP/IP connection to localhost, which (not being a UNIX socket) cannot use unix socket IDENT auth. It can use ident for passwordless auth anyway if you have an ident daemon running, but traditionally you'd use a password instead. For this to work, the server must be listening for TCP/IP connections (see postgresql.conf) and be set to accept password auth for at least the user and database you wish to log in to for 127.0.0.1/32 (localhost). now, I *thought* what that would do would be to allow me to issue the 'psql' command from my regular non-postgres Linux account and log in as long as I would issue: psql -d mydb -U postgres -W and then provide 'postgres' as the password as well, as I had changed it in mydb as previously stated. But I am denied access when I try this from my account. Because you're still using ident auth, as psql defaults to a UNIX socket. It never uses your password because it already knows you're not authorized according to pg_hba.conf's configuration for local UNIX sockets. Use a TCP/IP connection instead by adding the argument --host localhost to the psql command line. I cannot even confirm that the database is running as I thought it would, since I don't know what process to look for in the 'ps -ef' dump. postgres In any case, if the DB wasn't running you'd get connection refused errors or similar. $ sudo /etc/init.d/postgresql-8.3 stop [sudo] password for craig: * Stopping PostgreSQL 8.3 database server [ OK ] $ psql psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket /var/run/postgresql/.s.PGSQL.5432? $ psql -h 127.0.0.1 psql: could not connect to server: Connection refused Is the server running on host 127.0.0.1 and accepting TCP/IP connections on port 5432? -- 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
[GENERAL] Re: pg_restore fails on Windows
Magnus Hagander wrote Tom Tom wrote: Magnus Hagander wrote Tom Lane wrote: =?us-ascii?Q?Tom=20Tom?= [EMAIL PROTECTED] writes: Magnus Hagander wrote: Attached is a pg_restore.exe off CVS tip today, which should include the patch. Please try this one. I tested the restore using the provided pg_restore.exe. The output is: pg_restore: [archiver (db)] could not execute query: could not send data to server: No buffer space available (0x2747/10055) According to http://support.microsoft.com/kb/201213 this is an acknowledged bug that's been broken since Windows 95, so I suppose we should conclude that M$ is unwilling or incompetent to fix it. Yup, I was just reading that one when I saw your email. I finally got around to building a libpq with this change in it - attached here. Tom (not Lane), can you test this please? It shouldn't be this one really, since it doesn't list any modern Windows versions as having this issue, but it's worth a try. Tested. The restore comes through successfuly with the patched libpq. So I take it that it's caused by the MS issue. Again, we are using WinXP Professional SP2. Perhaps the system buffer space was _increased_ in XP (10MB comes through easily), still if the block is too large, it occurs (speculation). Yes, that sounds quite likely. They fixed the symptoms, but not the underlying problem. Since I don't know the implementation details of the patch I'd like to ask: 1.This is not official patch, didn't pass the review/test cycle; do you think that it can be used in the production environment (any side effects or so..)? If not, is the patch due for a next version? I plan to apply it to HEAD and supported back-branches (8.3 and 8.2) now that you have verified that it works, so it will be in the next versions. The only potential side-effect is that it will be slightly slower on packets 64kb, but I doubt that's even measurable in most cases. So yes, it should be safe to use in production. 2.Our production PG version is 8.1.3. For some reasons it is not possible to upgrade to the LATEST; I tested the libpq also on this version and it worked. Is it OK? I mean, did it worked by chance or the library API contracts didn't change between this version and latest? Note that libpq is only the *client* side. There is no patch necessary on the server. It might be easier to upgrade than the server? This I didn't know/realize. It's good enough for us to use only the *client* side from the HEAD. I tried the pg_restore from HEAD + patched libpq (on 8.1 installation) and it complained about missing zlib1 library. When supplied, next was libintl3 dll. Further I didn't check. Obviously the library dependencies have changed since the 8.1. How can I tell, which libraries/executables/resources of the installation are part of the *client* side (namely pg_restore), so that I can use it independently from the server version? Did you test it with the pg_restore that I sent, or with the one from 8.1? The pg_restore I sent was for HEAD, as well as the libpq I sent, so you shouldn't use those in production long-term. For binaries, we don't provide backpatches for 8.1 any more (it's not a supported platform on Windows!), but you might be able to use the latest 8.2 libpq with the 8.1 pg_restore - you'll have to try that once the release is eventually out. Or you can just apply the patch to the latest 8.1 libpq and build it yourself, of course. I think it should apply just fine. Tomas -- 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] pg_restore fails on Windows
2.Our production PG version is 8.1.3. For some reasons it is not possible to upgrade to the LATEST; I tested the libpq also on this version and it worked. Is it OK? I mean, did it worked by chance or the library API contracts didn't change between this version and latest? Note that libpq is only the *client* side. There is no patch necessary on the server. It might be easier to upgrade than the server? This I didn't know/realize. It's good enough for us to use only the *client* side from the HEAD. I tried the pg_restore from HEAD + patched libpq (on 8.1 installation) and it complained about missing zlib1 library. When supplied, next was libintl3 dll. Further I didn't check. Obviously the library dependencies have changed since the 8.1. How can I tell, which libraries/executables/resources of the installation are part of the *client* side (namely pg_restore), so that I can use it independently from the server version? That is correct, there have been a lot of changes there - mainly in that 8.3 and up are compiled with MSVC which changes some dependencies. I would suggest you install the latest 8.3 from the MSI installer. You can then select the client side only. It is no problem to have both 8.1 and 8.3 installed on the same machine at the same time (or you can install it on a different machine and just copy the necessary files over) //Magnus -- 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] Fwd: Restarting with pg_ctl, users, and passwords.
Thanks Craig and Tom, This is very helpful... once I troubleshoot accessing the database with the postgres user, I plan on adding other users with more restrictive credentials... I thought I'd start with this config just to see if I could get access on a very basic level. Off to google postgresql.conf and pg_hba.conf... Any suggested pages for learning this? Thanks again, Matt On Wed, Aug 20, 2008 at 8:18 AM, Craig Ringer [EMAIL PROTECTED] wrote: Matthew Pettis wrote: I've created a database and can log into it and do stuff with the tables using psql. However, I have a CGI app that wants to call the database (all on the same machine), but gets a 'FATAL: Ident authentication failed for user postgres' error. You *REALLY* shouldn't use the `postgres' user for applications or normal use. Create a new non-superuser, grant them the required priveleges, and use them for the web application. If you like you can make them the owner of a new database, so they can do basically whatever they want with that database but not mess with your others. See the documentation for CREATE USER, CREATE ROLE, GRANT, and CREATE DATABASE . now, while logged onto my Linux user account 'postgres', I can psql into my database without having to provide a password. It's using ident authentication on a UNIX socket connection. The database knows you are logged in on the UNIX account postgres (or have su'd to it) because the operating system can tell it that. So it trusts you. The web app will be using a TCP/IP connection to localhost, which (not being a UNIX socket) cannot use unix socket IDENT auth. It can use ident for passwordless auth anyway if you have an ident daemon running, but traditionally you'd use a password instead. For this to work, the server must be listening for TCP/IP connections (see postgresql.conf) and be set to accept password auth for at least the user and database you wish to log in to for 127.0.0.1/32 (localhost). now, I *thought* what that would do would be to allow me to issue the 'psql' command from my regular non-postgres Linux account and log in as long as I would issue: psql -d mydb -U postgres -W and then provide 'postgres' as the password as well, as I had changed it in mydb as previously stated. But I am denied access when I try this from my account. Because you're still using ident auth, as psql defaults to a UNIX socket. It never uses your password because it already knows you're not authorized according to pg_hba.conf's configuration for local UNIX sockets. Use a TCP/IP connection instead by adding the argument --host localhost to the psql command line. I cannot even confirm that the database is running as I thought it would, since I don't know what process to look for in the 'ps -ef' dump. postgres In any case, if the DB wasn't running you'd get connection refused errors or similar. $ sudo /etc/init.d/postgresql-8.3 stop [sudo] password for craig: * Stopping PostgreSQL 8.3 database server [ OK ] $ psql psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket /var/run/postgresql/.s.PGSQL.5432? $ psql -h 127.0.0.1 psql: could not connect to server: Connection refused Is the server running on host 127.0.0.1 and accepting TCP/IP connections on port 5432? -- Craig Ringer -- It is from the wellspring of our despair and the places that we are broken that we come to repair the world. -- Murray Waas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] CREATE DOMAIN with referential integrity
HI all. Is there a way to CREATE a DOMAIN in v8.3 with a CHECK() predicate to ensure the value is in a dictionary table? Of course, I already know the trivial and ugly solution with a (PL/Pg)SQL lookup function. Thanks.
Re: [GENERAL] schema name in SQL statement.
On Tuesday 19 August 2008 9:53:11 pm johnf wrote: On Tuesday 19 August 2008 04:01:55 pm Adrian Klaver wrote: -- Original message -- From: Masis, Alexander (US SSA) [EMAIL PROTECTED] I have to explicitly specify the schema name to make SQL statement to work. Can I set the schema before the query, or set a default schema? My current statement: SELECT col FROM schema.table I like to be able to use generic SQL statement like: SELECT col FROM table -- See search_path in: http://www.postgresql.org/docs/8.3/interactive/runtime-config-client.html -- Adrian Klaver [EMAIL PROTECTED] I see answers like this all the time. When I review the doc's I still don't know how to set the search_path because there is no example in the doc's. Do I do something like this: select search_path=(public) or select search_path=public . So how is the search_path set? -- John Fabiani From postgresql.conf.sample in ~pgsql/share: #--- # CLIENT CONNECTION DEFAULTS #--- # - Statement Behavior - #search_path = '$user,public' # schema names This is how the initial postgresql.conf is set up when you do an initdb. I have found the postgresql.conf to be quite well commented. -- Adrian Klaver [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Modeling tools
Hi, does anyone have any experience with MicroOLAP Database Designer for PostgreSQL? I've been looking at modeling tools and it appears there's really only a couple that support postgresql. CaseStudio2 *was* a pretty good tool but then it was acquired by Quest Software and became the Toad Data Modeler. Looks like it may still be a good tool but I can't seem to get the sales staff to return my calls so I'm exploring alternatives, which is how I found MicroOLAP. Just wondering if anyone has tried to use this tool for schema modeling and DDL generation. Thanks. - larry
[GENERAL]
Thank you Michael! I have another question - related to this. If I make changes to the postgresql.conf and then do a 'pg_ctl reload', how do I get the date time of this event? Cheers! Alexi Gen On Mon, Aug 18, 2008 at 03:53:32AM -0700, Alexi Gen wrote: Is there a table/view available from where I can check what time the cluster was started? Need this to calculate the uptime of the cluster. In PostgreSQL 8.1 and later you can run these queries to get the start time and uptime: SELECT pg_postmaster_start_time(); SELECT now() - pg_postmaster_start_time(); -- Michael Fuhr Send instant messages to your online friends http://uk.messenger.yahoo.com
[GENERAL] Time of 'pg_ctl reload'.
Thank you Michael! I have another question - related to this. If I make changes to the postgresql.conf and then do a 'pg_ctl reload', how do I get the date time of this event? Cheers! Alexi Gen On Mon, Aug 18, 2008 at 03:53:32AM -0700, Alexi Gen wrote: Is there a table/view available from where I can check what time the cluster was started? Need this to calculate the uptime of the cluster. In PostgreSQL 8.1 and later you can run these queries to get the start time and uptime: SELECT pg_postmaster_start_time(); SELECT now() - pg_postmaster_start_time(); -- Michael Fuhr Send instant messages to your online friends http://uk.messenger.yahoo.com
[GENERAL] negative values for vacuum threshold check !!!
Hello all While debugging my autovacuum I increased the level of logging to debug3 and got this: # cat /var/pgsql/data/logfile | grep vac | egrep mydb|mytable LOG: autovacuum: processing database mydb DEBUG: mytbl: vac: 10409 (threshold 20), anl: -183366 (threshold 5) LOG: autovacuum: processing database mydb DEBUG: mytbl: vac: 10966 (threshold 20), anl: -182489 (threshold 5) LOG: autovacuum: processing database mydb DEBUG: mytbl: vac: 11545 (threshold 20), anl: -181592 (threshold 5) Should I be worried with the negative values in 'anl' ? thx joao -- 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] schema name in SQL statement.
On Wed, Aug 20, 2008 at 8:13 AM, Adrian Klaver [EMAIL PROTECTED] wrote: From postgresql.conf.sample in ~pgsql/share: #--- # CLIENT CONNECTION DEFAULTS #--- # - Statement Behavior - #search_path = '$user,public' # schema names This is how the initial postgresql.conf is set up when you do an initdb. I have found the postgresql.conf to be quite well commented. I have a question (not necessarily to you...) . How come that syntax parses for postgresql.conf, but in psql it doesn't: set search_path = '$user,public' ; ERROR: schema $user,public does not exist I have to use this: set search_path = $user,public ; SET -- 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] Time of 'pg_ctl reload'.
If I make changes to the postgresql.conf and then do a 'pg_ctl reload', how do I get the date time of this event? it will be added in 8.4: http://www.depesz.com/index.php/2008/05/05/waiting-for-84-pg_conf_load_time-time-related-generate_series-and-enum-values-in-dt/ depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: [EMAIL PROTECTED] / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- 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] schema name in SQL statement.
--- On Tue, 8/19/08, Masis, Alexander (US SSA) [EMAIL PROTECTED] wrote: From: Masis, Alexander (US SSA) [EMAIL PROTECTED] Subject: [GENERAL] schema name in SQL statement. To: pgsql-general@postgresql.org Date: Tuesday, August 19, 2008, 10:52 PM I have to explicitly specify the schema name to make SQL statement to work. Can I set the schema before the query, or set a default schema? My current statement: SELECT col FROM schema.table I like to be able to use generic SQL statement like: SELECT col FROM table -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general you can change the search path of the schema SET search_path TO myschema -- 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] Fwd: Restarting with pg_ctl, users, and passwords.
Hi Craig, Ok, I changed my pg_hba.conf from the commented line to the following line to try to open things up for now (I know I will need more security, but I'm trying to open things up wide to find the problem): --- pg_hba.conf # IPv4 local connections: #hostall all 127.0.0.1/32 md5 hostall all 127.0.0.1/32 trust --- and made the following change to postgresql.conf (the line was previously commented out): --- postgresql.conf listen_addresses = 'localhost' --- I still am getting the same error from my application (which I believe is passing on some postgresql error info: --- App error Error reported was #39;FATAL: Ident authentication failed for user quot;postgresquot; #39;. This error occured when trying to make a connection to the specified postgresql server. Most commonly this is caused by (1) incorrect connection string (2) you didnt specify a #39;user=...#39; in your connection string (3) the postmaster (postgresql server) isnt running (4) you are not allowing TCP/IP connection to the postmaster (5) your postmaster is not running on the correct port - if its not on 5432 you must specify a #39;port=...#39; (6) the security on your system does not allow the webserver (usually user #39;nobody#39;) to make socket connections to the postmaster (7) you forgot to specify a #39;host=...#39; if the postmaster is on a different machine (8) you made a typo Any suggestions? I'm using the postgres user and password in my CGI app (though that shouldn't matter with my current config, right?) Help is appreciated, Matt On Wed, Aug 20, 2008 at 8:18 AM, Craig Ringer [EMAIL PROTECTED] wrote: Matthew Pettis wrote: I've created a database and can log into it and do stuff with the tables using psql. However, I have a CGI app that wants to call the database (all on the same machine), but gets a 'FATAL: Ident authentication failed for user postgres' error. You *REALLY* shouldn't use the `postgres' user for applications or normal use. Create a new non-superuser, grant them the required priveleges, and use them for the web application. If you like you can make them the owner of a new database, so they can do basically whatever they want with that database but not mess with your others. See the documentation for CREATE USER, CREATE ROLE, GRANT, and CREATE DATABASE . now, while logged onto my Linux user account 'postgres', I can psql into my database without having to provide a password. It's using ident authentication on a UNIX socket connection. The database knows you are logged in on the UNIX account postgres (or have su'd to it) because the operating system can tell it that. So it trusts you. The web app will be using a TCP/IP connection to localhost, which (not being a UNIX socket) cannot use unix socket IDENT auth. It can use ident for passwordless auth anyway if you have an ident daemon running, but traditionally you'd use a password instead. For this to work, the server must be listening for TCP/IP connections (see postgresql.conf) and be set to accept password auth for at least the user and database you wish to log in to for 127.0.0.1/32 (localhost). now, I *thought* what that would do would be to allow me to issue the 'psql' command from my regular non-postgres Linux account and log in as long as I would issue: psql -d mydb -U postgres -W and then provide 'postgres' as the password as well, as I had changed it in mydb as previously stated. But I am denied access when I try this from my account. Because you're still using ident auth, as psql defaults to a UNIX socket. It never uses your password because it already knows you're not authorized according to pg_hba.conf's configuration for local UNIX sockets. Use a TCP/IP connection instead by adding the argument --host localhost to the psql command line. I cannot even confirm that the database is running as I thought it would, since I don't know what process to look for in the 'ps -ef' dump. postgres In any case, if the DB wasn't running you'd get connection refused errors or similar. $ sudo /etc/init.d/postgresql-8.3 stop [sudo] password for craig: * Stopping PostgreSQL 8.3 database server [ OK ] $ psql psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket /var/run/postgresql/.s.PGSQL.5432? $ psql -h 127.0.0.1 psql: could not connect to server: Connection refused Is the server running on host 127.0.0.1 and accepting TCP/IP connections on port 5432? -- Craig Ringer -- It is from the wellspring of our despair and the places that we are broken that we come to repair the world. --
Re: [GENERAL] What's size of your PostgreSQL Database?
Just out of curiosity, how do you replicate that amount of data? When I started working here, we used Slony-I to replicate our aggregate fact tables. A little over a year ago our data volume had grown to the point that the Slony was regularly unable to keep up with the data volume and around this time Slony hit us with some crazy rollback based data loss bug. We elected to move our aggregate fact tables off of slony, but left metadata tables on Slony. So I wrote a custom in house replication engine into our aggregation process. Replication is accomplished in parallel via piped copy statements, and so far it's been fast enough to keep up with the data volume. Without getting into too much detail, an import process might look like this: - Obtain and transform data into a usable state - Import Data, and earmark it to be reaggregated - Reaggregate (summarize) the changed data - Copy the new data to all databases in the replication set - Integrate new data into the aggregate fact tables I'd be very interested in a developers view of running and maintaining a database this size. Mostly what choices is made during development that might have been different on a smaller database. I'm also curious about the maintenance needed to keep a database this size healthy over time. Ok, so all of this comes from what might be termed as an ETL Developer point of view. I pretty much only work on the data warehouse's import/summarization process and look into performance issues. Design: - Natural keys are pretty well strictly forbidden. Don't make a VARCHAR your primary key, please. ;-) - The Data determines partition criteria and the application layer is partition aware. - It's important to have aggregate tables to support common queries. Joins are ok, but repeatedly aggregating thousands of rows together on the fly really takes too much time. - Aggregation processes have to be designed with care. - Parallel processing is important, especially if you ever have to reprocess large amounts of data (for example due to incorrect initial data) Maintenance: - Autovacuum might be more trouble than it's worth. We frequently have mysteriously hung queries that are eventually traced back to being blocked by Autovacuum. The application layer knows exactly what and when is changing... it could *probably* take over this duty. - Pg upgrades are a major PITA and require absurd amounts of downtime for the data processing part of the warehouse. - Queries that have been working for long periods of time and suddenly stop working or hang are usually broken by statistics issues. - Partitioning is important because it allows the aggregate tables to be clustered, backed up, and archived individually If anyone wants to chat with me or direct me to resources about running Postgres on distributed file systems (or other grid computing solutions) please let me know. Yes, I'm aware of the proprietary solutions, but they've been ruled out on account of performance problems with aggregates and absurd licensing costs. Also, any grid computing solution that I write can easily be applied across the company and thus save us (quite literally) millions per year. Again, if you'd like more information about any particular topic, just ask. :) -Mark -- 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] Silent install 8.3 diiffers from 8.2
Has this issue been resolved by anyone? I can install over and over manually but any attempt at silent install fails with the same. Service 'PostgreSQL Database Server 8.3' (pgsql-8.3) failed to start. Verify that you have sufficient privileges to start system services. It works fine with 8.3.0 but not 8.3.3, what changes were made to the installer? I can verify that the previous version is un-installed, make sure the postgres\8.3 directory is clear, and reboot the machine to verify that no leftover processes remain? It still fails every time on silent install, however a manual install blasts right through with no problems. I would like to hand my sa a script and say just run this rather than hand him a script caviated with Oh and by the way you have to do manual install on all 30 machines.
Re: [GENERAL] SELECT query experts, anyone?
If you put this in the application, you could do something such as:
my @manufacturers = fetch(select manufacturer_no, name from
manufacturers);
my @select_fields = ('product_no');
foreach my $manufacturer (@manufacturers) {
my $manuf_no = $manufacturer-{manufacturer_no};
my $name = $manufacturer-{name};
push(@select_fields, case when x.manufacturer_no = $manuf_no then
'$name' else null end as manuf_${manuf_no}_products);
}
my @outer_select_fields = ('product_no', map { my $manuf = manuf_ .
$_-{manufacturer_no} . _products; sum($manuf) as $manuf }
@manufacturers);
my @dataset = fetch(
select @{[ join(,\n, @outer_select_fields) ]}
from (
select @{[ join(,\n, @select_fields) ]}
from products_by_manufacturer x
) x
group by product_no
Uh, or something like that. Perl in Evolution is really.. painful.
-Mark
On Wed, 2008-08-20 at 15:50 +0300, Teemu Juntunen wrote:
Hi Experts,
is threre any way to SELECT values in columns instead of rows? For
example select products and their manufacters in the followin way:
product1; manufacturer1; manufacturer2;,,, manufacturerN
product2; manufacturer3;
product3; manufacturer1;.. manufacturerN-1
With a function you could put the product manufacturers in one string,
but I would like to have them in columns.
How about arrays. Is there a way to SELECT values in an array to
columns?
Best regards and thanks,
Teemu Juntunen
--
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] Is the primary key constraint also an index?
On 13/08/2008 03:18, Tim Uckun wrote: If I have a primary key constraint defined in the database do I also need to create an index on that field for fast lookup? As I understand it, creating a primary key on a table also creates an index automatically: testdb=# create table test(f1 integer, f2 text); CREATE TABLE testdb=# alter table test add constraint test_pk primary key(f1); NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index test_pk for table test ALTER TABLE Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] function SETOF return type with variable columns?
Greetings, Is it possible to have a function with a return type of SETOF that has variable number of return columns? The input parameter for this function will be a String containing a number of codes separated by a tilde character. I would like to have 1 output column for each of these codes, but the number of input codes may change for each time the function is called. For example: Input: ABC1~XYZ2~MNO3 Output result set will then look like this where name, ABC1, XYZ2, and MNO3 are column headers and not a data row: name | ABC1 | XYZ2 | MNO3 bob | 9 | 3 | 1 john | 5 | 2 | 1 ... Every row in the output set will contain a name and then a count of the number of codes matched for that name. But the codes queried for would change with each call of the function. Does it make sense what I am asking for? Thanks in advance, James -- 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] Fwd: Restarting with pg_ctl, users, and passwords.
On Wed, Aug 20, 2008 at 11:38 AM, Matthew Pettis [EMAIL PROTECTED] wrote: Hi Craig, Ok, I changed my pg_hba.conf from the commented line to the following line to try to open things up for now (I know I will need more security, but I'm trying to open things up wide to find the problem): --- pg_hba.conf # IPv4 local connections: #hostall all 127.0.0.1/32 md5 hostall all 127.0.0.1/32 trust --- and made the following change to postgresql.conf (the line was previously commented out): --- postgresql.conf listen_addresses = 'localhost' --- I still am getting the same error from my application (which I believe is passing on some postgresql error info: --- App error Error reported was #39;FATAL: Ident authentication failed for user quot;postgresquot; #39;. You need to restart the postgresql service for the listen_addresses to take effect, and either restarting or reloading will make the changes you made in pg_hba.conf take effect. -- 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] WAL archiving to network drive
Greg Smith wrote: 2) If there is a problem with the network drive, the slow/blocked network write is not happening where the database is waiting for it. This is not really a consideration, is it? I was under the impression that no other database activity blocks waiting for the archiver. That doesn't even make sense. 3) In an emergency where the usual network drive is unavailable, you can temporarily change your archive strategy without touching the database server's configuration. That's just as true with the direct write method. You simply change the archive script to do nothing and return a failed exit status. The DBMS will keep trying until you change it back, and then everything catches up and proceeds as normal. -Glen -- 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] Fwd: Restarting with pg_ctl, users, and passwords.
SOLVED. Yep, Restart was done. The issue turned out not to be with Postgresql config, but the app config. In the app, I define a connection string, which has user, password, and databasename. When I had this same configuration on WinXP, I did not need to specify a fourth parameter, the host, which explicitly told the app to use host=localhost. When I added the host param to the connection string, it all went through. On the bright side, I learned a lot about how to restart the service and the config files... Curious: Any ideas why I can leave the host off my connection string in WinXP, but not Linux? It it an idiosyncracy of my app, or of PostgreSQL? Thanks for all the help, Matt On Wed, Aug 20, 2008 at 1:27 PM, Scott Marlowe [EMAIL PROTECTED] wrote: On Wed, Aug 20, 2008 at 11:38 AM, Matthew Pettis [EMAIL PROTECTED] wrote: Hi Craig, Ok, I changed my pg_hba.conf from the commented line to the following line to try to open things up for now (I know I will need more security, but I'm trying to open things up wide to find the problem): --- pg_hba.conf # IPv4 local connections: #hostall all 127.0.0.1/32 md5 hostall all 127.0.0.1/32 trust --- and made the following change to postgresql.conf (the line was previously commented out): --- postgresql.conf listen_addresses = 'localhost' --- I still am getting the same error from my application (which I believe is passing on some postgresql error info: --- App error Error reported was #39;FATAL: Ident authentication failed for user quot;postgresquot; #39;. You need to restart the postgresql service for the listen_addresses to take effect, and either restarting or reloading will make the changes you made in pg_hba.conf take effect. -- It is from the wellspring of our despair and the places that we are broken that we come to repair the world. -- Murray Waas -- 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] function SETOF return type with variable columns?
Hello
2008/8/20 James Neff [EMAIL PROTECTED]:
Greetings,
Is it possible to have a function with a return type of SETOF that has
variable number of return columns?
No. Number and result types have to be known in parse time. Use array
without it.
The input parameter for this function will be a String containing a number
of codes separated by a tilde character. I would like to have 1 output
column for each of these codes, but the number of input codes may change for
each time the function is called.
For example:
Input: ABC1~XYZ2~MNO3
simply string_to_array('ABC1~XYZ2~MNO3', '~');
Output result set will then look like this where name, ABC1, XYZ2, and MNO3
are column headers and not a data row:
name | ABC1 | XYZ2 | MNO3
bob | 9 | 3 | 1
john | 5 | 2 | 1
...
Every row in the output set will contain a name and then a count of the
number of codes matched for that name. But the codes queried for would
change with each call of the function.
Does it make sense what I am asking for?
it has sense, but postgresql doesn't support this feature
Regards
Pavel Stehule
Thanks in advance,
James
--
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] WAL archiving to network drive
On Wed, 20 Aug 2008, Glen Parker wrote: Greg Smith wrote: 2) If there is a problem with the network drive, the slow/blocked network write is not happening where the database is waiting for it. This is not really a consideration, is it? I was under the impression that no other database activity blocks waiting for the archiver. The database will continue accumulating WAL segments it can't recycle if the archiver keeps failing, which can cause the size of the pg_xlog directory (often mounted into a separate, smaller partition or disk) to increase dramatically. You do not want to be the guy who caused the database to go down because the xlog disk filled after some network mount flaked out. I've seen that way too many times in WAN environments where the remote location was unreachable for days, due to natural disaster for example, and since under normal operation pg_xlog never got very big it wasn't sized for that. It will also slow things down a bit under heavy write loads, as every segment change will result in creating a new segment file rather than re-using an old one. You also don't want to be the guy who has to explain why the database is taking hours to come back up again after it crashed and has 4000 WAL segments to replay, because archiving failed for a long time and prevented proper checkpoints (ask Robert Treat if you don't believe me, he also once was that guy). While occasional archiving failures are tolerated just fine and you can catchup later, a design that presumes it's OK for them to happen for extended periods is not optimal. A crash after an excess of segments have accumulated can result in a large amount of replay downtime for the server, and in some environments (where the primary is very redundant for example) the risk of that is worse than saying your PITR backup is hosed and just throwing away the segments if you can't transfer them to the backup for some reason. 3) In an emergency where the usual network drive is unavailable, you can temporarily change your archive strategy without touching the database server's configuration. That's just as true with the direct write method. You simply change the archive script to do nothing and return a failed exit status. The DBMS will keep trying until you change it back, and then everything catches up and proceeds as normal. I've already been through why assuming archive failures have zero cost can be bad above. There are some additional issues here as well. When's the last time you tried to get a DBA to send a kill -HUP to a production database to get it to recognize that archive_command was changed in the postgresql.conf? Sure, we all know it's not supposed to ever hurt the server, but Murphy is a tricky character and lots of people are (rightly) paranoid. I think we've all known admins whose fingers were trained to only ever type kill -9 no matter what signal they meant to send. OK, maybe you're smarter than that and used a separate script. DBAs are also not happy changing a script that gets called by the database every couple of minutes, and as soon as there's more than one piece involved it can be difficult to do an atomic update of said script. Anytime you can decouple a failure recovery cleanup (like a network drive getting disconnected screwing up archiving) completely away from the routine database operation, you have gained flexibility in how easily and safely you can recover from that failure. I assure you that all the suggestions I made come from painful and completely unexpected messes -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- 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] Fwd: Restarting with pg_ctl, users, and passwords.
On Wed, Aug 20, 2008 at 01:58:37PM -0500, Matthew Pettis wrote: Curious: Any ideas why I can leave the host off my connection string in WinXP, but not Linux? It it an idiosyncracy of my app, or of PostgreSQL? No host specified on a UNIX system means unix domain socket which is essentially a way of doing a socket connection on the local machine. You can acheive the same effect by host=/path/to/socket. Leaving off the host in windows I'm not sure what it does. Have a ncie day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] Fwd: Restarting with pg_ctl, users, and passwords.
-- Original message -- From: Matthew Pettis [EMAIL PROTECTED] SOLVED. Yep, Restart was done. The issue turned out not to be with Postgresql config, but the app config. In the app, I define a connection string, which has user, password, and databasename. When I had this same configuration on WinXP, I did not need to specify a fourth parameter, the host, which explicitly told the app to use host=localhost. When I added the host param to the connection string, it all went through. On the bright side, I learned a lot about how to restart the service and the config files... Curious: Any ideas why I can leave the host off my connection string in WinXP, but not Linux? It it an idiosyncracy of my app, or of PostgreSQL? Thanks for all the help, Matt Is the Linux app running on the Postgres server machine? If so I hazard a guess that you have a line like: local all all trust before your host line in pg_hba. The app connecting from the same machine would try the local socket (local) before the localhost(tcp/ip), unless localhost was specified in the connection string. -- Adrian Klaver [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] order by x using varchar_pattern_ops
OK, so I know about varchar_pattern_ops for things like matching with like in a non C locale. But is there a way to force a use of varchar_pattern_ops on a column used in an order by? -- 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] Fwd: Restarting with pg_ctl, users, and passwords.
Below is the pg_hba.conf file exerpt (minus a lot of comments)... the line starting '#host' was my attempt at opening up the db as wide as possible just to see if i could get in somehow... --- pg_hba.conf -- # Database administrative login by UNIX sockets local all postgres ident sameuser # TYPE DATABASEUSERCIDR-ADDRESS METHOD # local is for Unix domain socket connections only local all all ident sameuser # IPv4 local connections: hostall all 127.0.0.1/32 md5 #hostall all 127.0.0.1/32 trust # IPv6 local connections: hostall all ::1/128 md5 -- On Wed, Aug 20, 2008 at 3:32 PM, Adrian Klaver [EMAIL PROTECTED] wrote: -- Original message -- From: Matthew Pettis [EMAIL PROTECTED] SOLVED. Yep, Restart was done. The issue turned out not to be with Postgresql config, but the app config. In the app, I define a connection string, which has user, password, and databasename. When I had this same configuration on WinXP, I did not need to specify a fourth parameter, the host, which explicitly told the app to use host=localhost. When I added the host param to the connection string, it all went through. On the bright side, I learned a lot about how to restart the service and the config files... Curious: Any ideas why I can leave the host off my connection string in WinXP, but not Linux? It it an idiosyncracy of my app, or of PostgreSQL? Thanks for all the help, Matt Is the Linux app running on the Postgres server machine? If so I hazard a guess that you have a line like: local all all trust before your host line in pg_hba. The app connecting from the same machine would try the local socket (local) before the localhost(tcp/ip), unless localhost was specified in the connection string. -- Adrian Klaver [EMAIL PROTECTED] -- It is from the wellspring of our despair and the places that we are broken that we come to repair the world. -- Murray Waas -- 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] Fwd: Restarting with pg_ctl, users, and passwords.
-- Original message -- From: [EMAIL PROTECTED] (Adrian Klaver) -- Original message -- From: Matthew Pettis [EMAIL PROTECTED] SOLVED. Yep, Restart was done. The issue turned out not to be with Postgresql config, but the app config. In the app, I define a connection string, which has user, password, and databasename. When I had this same configuration on WinXP, I did not need to specify a fourth parameter, the host, which explicitly told the app to use host=localhost. When I added the host param to the connection string, it all went through. On the bright side, I learned a lot about how to restart the service and the config files... Curious: Any ideas why I can leave the host off my connection string in WinXP, but not Linux? It it an idiosyncracy of my app, or of PostgreSQL? Thanks for all the help, Matt Is the Linux app running on the Postgres server machine? If so I hazard a guess that you have a line like: local all all trust Should have been: local all allsome non-functional authentication method this would cause the connection to the socket to fail assuming the authentication method selected did not work. before your host line in pg_hba. The app connecting from the same machine would try the local socket (local) before the localhost(tcp/ip), unless localhost was specified in the connection string. -- Adrian Klaver [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Adrian Klaver [EMAIL PROTECTED] -- 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] Fwd: Restarting with pg_ctl, users, and passwords.
-- Original message -- From: Matthew Pettis [EMAIL PROTECTED] Below is the pg_hba.conf file exerpt (minus a lot of comments)... the line starting '#host' was my attempt at opening up the db as wide as possible just to see if i could get in somehow... --- pg_hba.conf -- # Database administrative login by UNIX sockets local all postgres ident sameuser The line above is the reason you have to specify localhost in your connection string. From: http://www.postgresql.org/docs/8.3/interactive/auth-pg-hba-conf.html Each record specifies a connection type, a client IP address range (if relevant for the connection type), a database name, a user name, and the authentication method to be used for connections matching these parameters. The first record with a matching connection type, client address, requested database, and user name is used to perform authentication. There is no fall-through or backup: if one record is chosen and the authentication fails, subsequent records are not considered. If no record matches, access is denied. Unless you are logged in as the OS user postgres and connecting as such the local line will fail and the connection will be aborted. To skip over the local line and get the host line you have specify localhost in your connection string. # TYPE DATABASEUSERCIDR-ADDRESS METHOD # local is for Unix domain socket connections only local all all ident sameuser # IPv4 local connections: hostall all 127.0.0.1/32 md5 #hostall all 127.0.0.1/32 trust # IPv6 local connections: hostall all ::1/128 md5 -- On Wed, Aug 20, 2008 at 3:32 PM, Adrian Klaver [EMAIL PROTECTED] wrote: -- Original message -- From: Matthew Pettis [EMAIL PROTECTED] SOLVED. Yep, Restart was done. The issue turned out not to be with Postgresql config, but the app config. In the app, I define a connection string, which has user, password, and databasename. When I had this same configuration on WinXP, I did not need to specify a fourth parameter, the host, which explicitly told the app to use host=localhost. When I added the host param to the connection string, it all went through. On the bright side, I learned a lot about how to restart the service and the config files... Curious: Any ideas why I can leave the host off my connection string in WinXP, but not Linux? It it an idiosyncracy of my app, or of PostgreSQL? Thanks for all the help, Matt Is the Linux app running on the Postgres server machine? If so I hazard a guess that you have a line like: local all all trust before your host line in pg_hba. The app connecting from the same machine would try the local socket (local) before the localhost(tcp/ip), unless localhost was specified in the connection string. -- Adrian Klaver [EMAIL PROTECTED] -- Adrian Klaver [EMAIL PROTECTED] -- 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] Fwd: Restarting with pg_ctl, users, and passwords.
would the 'ident sameuser' entry qualify as a 'some non-functional authentication method'? On Wed, Aug 20, 2008 at 3:48 PM, Adrian Klaver [EMAIL PROTECTED] wrote: -- Original message -- From: [EMAIL PROTECTED] (Adrian Klaver) -- Original message -- From: Matthew Pettis [EMAIL PROTECTED] SOLVED. Yep, Restart was done. The issue turned out not to be with Postgresql config, but the app config. In the app, I define a connection string, which has user, password, and databasename. When I had this same configuration on WinXP, I did not need to specify a fourth parameter, the host, which explicitly told the app to use host=localhost. When I added the host param to the connection string, it all went through. On the bright side, I learned a lot about how to restart the service and the config files... Curious: Any ideas why I can leave the host off my connection string in WinXP, but not Linux? It it an idiosyncracy of my app, or of PostgreSQL? Thanks for all the help, Matt Is the Linux app running on the Postgres server machine? If so I hazard a guess that you have a line like: local all all trust Should have been: local all allsome non-functional authentication method this would cause the connection to the socket to fail assuming the authentication method selected did not work. before your host line in pg_hba. The app connecting from the same machine would try the local socket (local) before the localhost(tcp/ip), unless localhost was specified in the connection string. -- Adrian Klaver [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Adrian Klaver [EMAIL PROTECTED] -- It is from the wellspring of our despair and the places that we are broken that we come to repair the world. -- Murray Waas -- 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] Fwd: Restarting with pg_ctl, users, and passwords.
-- Original message -- From: Matthew Pettis [EMAIL PROTECTED] would the 'ident sameuser' entry qualify as a 'some non-functional authentication method'? Yes. Basically you only get one shot at each connection to satisfy the requirements of a pg_hba line. The lines are read top to bottom, so if you have restrictive line at the top that your connection cannot satisfy then you are locked out. As has been mentioned on Linux the default action is to connect via the local socket in the absence of a host name/ip in the connection string.So in your case with no host specified the connection would attempt a socket connection. The first socket line is: local all postgres ident sameuser so you would need to be logged in as the Linux user postgres to make the connection. On Wed, Aug 20, 2008 at 3:48 PM, Adrian Klaver [EMAIL PROTECTED] wrote: -- Original message -- From: [EMAIL PROTECTED] (Adrian Klaver) -- Original message -- From: Matthew Pettis [EMAIL PROTECTED] SOLVED. Yep, Restart was done. The issue turned out not to be with Postgresql config, but the app config. In the app, I define a connection string, which has user, password, and databasename. When I had this same configuration on WinXP, I did not need to specify a fourth parameter, the host, which explicitly told the app to use host=localhost. When I added the host param to the connection string, it all went through. On the bright side, I learned a lot about how to restart the service and the config files... Curious: Any ideas why I can leave the host off my connection string in WinXP, but not Linux? It it an idiosyncracy of my app, or of PostgreSQL? Thanks for all the help, Matt Is the Linux app running on the Postgres server machine? If so I hazard a guess that you have a line like: local all all trust Should have been: local all allsome non-functional authentication method this would cause the connection to the socket to fail assuming the authentication method selected did not work. before your host line in pg_hba. The app connecting from the same machine would try the local socket (local) before the localhost(tcp/ip), unless localhost was specified in the connection string. -- Adrian Klaver [EMAIL PROTECTED] -- Adrian Klaver [EMAIL PROTECTED] -- 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] Fwd: Restarting with pg_ctl, users, and passwords.
So, since I run my CGI under a non-'postgres' user, is that the line that would govern my authentication, and then fail me? Because I thought with 'postgres' listed as the 3rd spot, this line would not apply, and would move on to a different governing rule... On Wed, Aug 20, 2008 at 4:21 PM, Adrian Klaver [EMAIL PROTECTED] wrote: -- Original message -- From: Matthew Pettis [EMAIL PROTECTED] would the 'ident sameuser' entry qualify as a 'some non-functional authentication method'? Yes. Basically you only get one shot at each connection to satisfy the requirements of a pg_hba line. The lines are read top to bottom, so if you have restrictive line at the top that your connection cannot satisfy then you are locked out. As has been mentioned on Linux the default action is to connect via the local socket in the absence of a host name/ip in the connection string.So in your case with no host specified the connection would attempt a socket connection. The first socket line is: local all postgres ident sameuser so you would need to be logged in as the Linux user postgres to make the connection. On Wed, Aug 20, 2008 at 3:48 PM, Adrian Klaver [EMAIL PROTECTED] wrote: -- Original message -- From: [EMAIL PROTECTED] (Adrian Klaver) -- Original message -- From: Matthew Pettis [EMAIL PROTECTED] SOLVED. Yep, Restart was done. The issue turned out not to be with Postgresql config, but the app config. In the app, I define a connection string, which has user, password, and databasename. When I had this same configuration on WinXP, I did not need to specify a fourth parameter, the host, which explicitly told the app to use host=localhost. When I added the host param to the connection string, it all went through. On the bright side, I learned a lot about how to restart the service and the config files... Curious: Any ideas why I can leave the host off my connection string in WinXP, but not Linux? It it an idiosyncracy of my app, or of PostgreSQL? Thanks for all the help, Matt Is the Linux app running on the Postgres server machine? If so I hazard a guess that you have a line like: local all all trust Should have been: local all allsome non-functional authentication method this would cause the connection to the socket to fail assuming the authentication method selected did not work. before your host line in pg_hba. The app connecting from the same machine would try the local socket (local) before the localhost(tcp/ip), unless localhost was specified in the connection string. -- Adrian Klaver [EMAIL PROTECTED] -- Adrian Klaver [EMAIL PROTECTED] -- It is from the wellspring of our despair and the places that we are broken that we come to repair the world. -- Murray Waas -- 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] Silent install 8.3 diiffers from 8.2
Jerel- any chance of running the postgres start script under god rights e.g. runas /noprofile /user:mymachine\GOD net start PostsgreSQL ? Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. Subject: Re: [GENERAL] Silent install 8.3 diiffers from 8.2 Date: Wed, 20 Aug 2008 12:43:44 -0500 From: [EMAIL PROTECTED] To: pgsql-general@postgresql.org RE: Silent install 8.3 diiffers from 8.2 Has this issue been resolved by anyone? I can install over and over manually but any attempt at silent install fails with the same. Service 'PostgreSQL Database Server 8.3' (pgsql-8.3) failed to start. Verify that you have sufficient privileges to start system services. It works fine with 8.3.0 but not 8.3.3, what changes were made to the installer? I can verify that the previous version is un-installed, make sure the postgres\8.3 directory is clear, and reboot the machine to verify that no leftover processes remain? It still fails every time on silent install, however a manual install blasts right through with no problems. I would like to hand my sa a script and say just run this rather than hand him a script caviated with Oh and by the way you have to do manual install on all 30 machines. _ Get thousands of games on your PC, your mobile phone, and the web with Windows®. http://clk.atdmt.com/MRT/go/108588800/direct/01/
Re: [GENERAL] Fwd: Restarting with pg_ctl, users, and passwords.
-- Original message -- From: Matthew Pettis [EMAIL PROTECTED] So, since I run my CGI under a non-'postgres' user, is that the line that would govern my authentication, and then fail me? Because I thought with 'postgres' listed as the 3rd spot, this line would not apply, and would move on to a different governing rule... Sorry I was being thick. I failed to see the second local line. Yes it should pick either the next local line if no host is specified or the next host line if one is specified. On Wed, Aug 20, 2008 at 4:21 PM, Adrian Klaver [EMAIL PROTECTED] wrote: -- Original message -- From: Matthew Pettis [EMAIL PROTECTED] would the 'ident sameuser' entry qualify as a 'some non-functional authentication method'? Yes. Basically you only get one shot at each connection to satisfy the requirements of a pg_hba line. The lines are read top to bottom, so if you have restrictive line at the top that your connection cannot satisfy then you are locked out. As has been mentioned on Linux the default action is to connect via the local socket in the absence of a host name/ip in the connection string.So in your case with no host specified the connection would attempt a socket connection. The first socket line is: local all postgres ident sameuser so you would need to be logged in as the Linux user postgres to make the connection. On Wed, Aug 20, 2008 at 3:48 PM, Adrian Klaver [EMAIL PROTECTED] wrote: -- Original message -- From: [EMAIL PROTECTED] (Adrian Klaver) -- Original message -- From: Matthew Pettis [EMAIL PROTECTED] SOLVED. Yep, Restart was done. The issue turned out not to be with Postgresql config, but the app config. In the app, I define a connection string, which has user, password, and databasename. When I had this same configuration on WinXP, I did not need to specify a fourth parameter, the host, which explicitly told the app to use host=localhost. When I added the host param to the connection string, it all went through. On the bright side, I learned a lot about how to restart the service and the config files... Curious: Any ideas why I can leave the host off my connection string in WinXP, but not Linux? It it an idiosyncracy of my app, or of PostgreSQL? Thanks for all the help, Matt Is the Linux app running on the Postgres server machine? If so I hazard a guess that you have a line like: local all all trust Should have been: local all allsome non-functional authentication method this would cause the connection to the socket to fail assuming the authentication method selected did not work. before your host line in pg_hba. The app connecting from the same machine would try the local socket (local) before the localhost(tcp/ip), unless localhost was specified in the connection string. -- Adrian Klaver [EMAIL PROTECTED] -- Adrian Klaver [EMAIL PROTECTED] -- Adrian Klaver [EMAIL PROTECTED] -- 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] Silent install 8.3 diiffers from 8.2
Well it is the administrator account running the install, don't think I could get any more privileged than that. Near as I can tell even though the message says check privileges, the installer really thinks there is another postgres running, however there is not. Even with previous installs wiped clean, and processes cleared out, even server reboot to make sure nothing is left over and restarted. If it is ran in interactive mode everything works fine. I can install interactive but I would prefer to script it. The original post in April Silent install 8.3 diiffers from 8.2 appeared to have the same problem. I was hoping someone had figured out what was wrong. I have had no problems silent installing 8.2 versions and I am able to get silent install to work with 8.3.0 however with same options 8.3.3 does not work. Basic options with just: INTERNALLAUNCH=1 SERVICEDOMAIN=MYDOMAIN SERVICEPASSWORD=MYSERVICEPW SUPERPASSWORD=MYPGPW PERMITTREMOTE=1 /qr Once it fails and unwinds I can still run silent 8.2.x, or silent 8.3.0, or interactive 8.3.3, but never works for silent 8.3.3! From: Martin Gainty [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2008 3:39 PM To: Blakely, Jerel (Mission Systems); pgsql-general@postgresql.org Subject: RE: [GENERAL] Silent install 8.3 diiffers from 8.2 Jerel- any chance of running the postgres start script under god rights e.g. runas /noprofile /user:mymachine\GOD net start PostsgreSQL ? Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. Subject: Re: [GENERAL] Silent install 8.3 diiffers from 8.2 Date: Wed, 20 Aug 2008 12:43:44 -0500 From: [EMAIL PROTECTED] To: pgsql-general@postgresql.org Has this issue been resolved by anyone? I can install over and over manually but any attempt at silent install fails with the same. Service 'PostgreSQL Database Server 8.3' (pgsql-8.3) failed to start. Verify that you have sufficient privileges to start system services. It works fine with 8.3.0 but not 8.3.3, what changes were made to the installer? I can verify that the previous version is un-installed, make sure the postgres\8.3 directory is clear, and reboot the machine to verify that no leftover processes remain? It still fails every time on silent install, however a manual install blasts right through with no problems. I would like to hand my sa a script and say just run this rather than hand him a script caviated with Oh and by the way you have to do manual install on all 30 machines. Get thousands of games on your PC, your mobile phone, and the web with Windows(r). Game with Windows http://clk.atdmt.com/MRT/go/108588800/direct/01/
[GENERAL] plpgsql - sorting result set
Is it possible to sort a result set in plpgsql? That is, after building up the result set with RETURN NEXT from multiple queries, I'd like to sort the set before returning. I'm still using 8.1 if that is an issue. Thanks, Bob Gobeille Hewlett Packard Open Source Program Office -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] ADO/ODBC returns TEXT fields as Varchar, not LongVarChar
I am new to PostgreSQL and running 8.3 on Windows. I am connecting using ADO and the ODBC driver. My connection string contains TextAsLongVarchar=1 and MaxLongVarcharSize=65536, however, my TEXT column is truncated at 255 bytes. What must I do to get ADO to recognize the TEXT field as a LongVarchar? -- .Bill. -- 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] WAL archiving to network drive
Greg Smith [EMAIL PROTECTED] writes: You also don't want to be the guy who has to explain why the database is taking hours to come back up again after it crashed and has 4000 WAL segments to replay, because archiving failed for a long time and prevented proper checkpoints (ask Robert Treat if you don't believe me, he also once was that guy). Say what? Archiver failure can't/shouldn't prevent checkpointing. 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
Re: [GENERAL] order by x using varchar_pattern_ops
Scott Marlowe [EMAIL PROTECTED] writes: But is there a way to force a use of varchar_pattern_ops on a column used in an order by? ORDER BY x USING ~~(asc direction) ORDER BY x USING ~~(desc direction) (assuming those are the right names of the operators, I'm too lazy to check) 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
Re: [GENERAL] plpgsql - sorting result set
On Wed, Aug 20, 2008 at 7:20 PM, Robert Gobeille [EMAIL PROTECTED] wrote: Is it possible to sort a result set in plpgsql? That is, after building up the result set with RETURN NEXT from multiple queries, I'd like to sort the set before returning. I'm still using 8.1 if that is an issue. Have you already ruled out: select * from (select * from your_func()) order by something? if so, why? merlin -- 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] function SETOF return type with variable columns?
On Wed, Aug 20, 2008 at 12:59 PM, James Neff [EMAIL PROTECTED] wrote: Greetings, Is it possible to have a function with a return type of SETOF that has variable number of return columns? The input parameter for this function will be a String containing a number of codes separated by a tilde character. I would like to have 1 output column for each of these codes, but the number of input codes may change for each time the function is called. For example: Input: ABC1~XYZ2~MNO3 Output result set will then look like this where name, ABC1, XYZ2, and MNO3 are column headers and not a data row: name | ABC1 | XYZ2 | MNO3 bob | 9 | 3 | 1 john | 5 | 2 | 1 ... Every row in the output set will contain a name and then a count of the number of codes matched for that name. But the codes queried for would change with each call of the function. Does it make sense what I am asking for? PostgreSQL functions are for the most part strictly bound to their return type. If you are willing to coerce everything to text, you might be able to return 'setof text[]' instead of a record. This may require more acrobatics inside the function than you really want to get in to (especially if you are getting into deep dynamic sql, iterating the column lists in information_schema and building queries). Another possibility is to make a custom type that has at least as many columns as you are likely to use, and make them all text...set the ones you want and leave the rest null. This is, uh, fairly lame but I'm trying to think outside the box here :-). merlin -- 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] function SETOF return type with variable columns?
Merlin Moncure [EMAIL PROTECTED] writes: PostgreSQL functions are for the most part strictly bound to their return type. There is, however, the trick of declaring the function as returns record and then specifying the names and types of the output columns in the calling query. I'm not sure how practical that is to use with a plpgsql function, and in any case it's not the syntax the OP asked for; but it seems worth mentioning in this thread. 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
Re: [GENERAL] ADO/ODBC returns TEXT fields as Varchar, not LongVarChar
Further testing has revealed that adding UnknownAsLongVarchar=1 to the connection string causes ADO to see TEXT fields as LongVarchar but it also causes all VARCHAR fields of any size to be seen as LongVarChar. Why is ADO or the ODBC driver seeing all Varchar and Text fields as type unknown? -- .Bill. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Single character bitfields
I have a large database and I want to have several fields (among many) that are single character fields (in fact they are bitfields). 1) Is char(1) the most efficient way to store these fields? If not what is better? 2) I need to test the field against constants, e.g if the field is called source then I need tests like: IF source = 0x10 THEN ... This does not work in plpgsql functions (source is character(1)), so what is the solution? Thanks Andrew -- ___ Andrew J. P. Maclean Centre for Autonomous Systems The Rose Street Building J04 The University of Sydney 2006 NSW AUSTRALIA Ph: +61 2 9351 3283 Fax: +61 2 9351 7474 URL: http://www.acfr.usyd.edu.au/ ___ -- 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] Single character bitfields
On Thu, 21 Aug 2008 12:40:29 +1000 Andrew Maclean [EMAIL PROTECTED] wrote: I have a large database and I want to have several fields (among many) that are single character fields (in fact they are bitfields). 1) Is char(1) the most efficient way to store these fields? If not what is better? boolean? Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- 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] plpgsql - sorting result set
On Aug 20, 2008, at 7:37 PM, Merlin Moncure wrote: On Wed, Aug 20, 2008 at 7:20 PM, Robert Gobeille [EMAIL PROTECTED] wrote: Is it possible to sort a result set in plpgsql? That is, after building up the result set with RETURN NEXT from multiple queries, I'd like to sort the set before returning. I'm still using 8.1 if that is an issue. Have you already ruled out: select * from (select * from your_func()) order by something? if so, why? merlin I've ruled this out because I do multiple queries. Here is my function. I want to reorder the result set (output table) before returning. CREATE or REPLACE function uploadtree2path(uploadtree_pk_in int) returns setof uploadtree as $$ DECLARE UTrec uploadtree; UTpkinteger; sql varchar; BEGIN UTpk := uploadtree_pk_in; WHILE UTpk 0 LOOP sql := 'select * from uploadtree where uploadtree_pk=' || UTpk; execute sql into UTrec; IF ((UTrec.ufile_mode (128)) = 0) THEN RETURN NEXT UTrec; END IF; UTpk := UTrec.parent; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; Thanks, Bob -- 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] Single character bitfields
On Aug 20, 2008, at 7:56 PM, Joshua Drake wrote: I have a large database and I want to have several fields (among many) that are single character fields (in fact they are bitfields). boolean? Out of curiosity, does postgres collapse multiple boolean columns to a bitfield internally? In other words, I guess I assume a single boolean column will always take 1 byte of space... but if I have 8 boolean columns, will they all fit into that 1 byte? -- 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] plpgsql - sorting result set
On Aug 20, 2008, at 10:11 PM, Gobeille, Robert wrote: On Aug 20, 2008, at 7:37 PM, Merlin Moncure wrote: On Wed, Aug 20, 2008 at 7:20 PM, Robert Gobeille [EMAIL PROTECTED] wrote: Is it possible to sort a result set in plpgsql? That is, after building up the result set with RETURN NEXT from multiple queries, I'd like to sort the set before returning. I'm still using 8.1 if that is an issue. Have you already ruled out: select * from (select * from your_func()) order by something? if so, why? merlin I've ruled this out because I do multiple queries. Here is my function. I want to reorder the result set (output table) before returning. CREATE or REPLACE function uploadtree2path(uploadtree_pk_in int) returns setof uploadtree as $$ DECLARE UTrec uploadtree; UTpkinteger; sql varchar; BEGIN UTpk := uploadtree_pk_in; WHILE UTpk 0 LOOP sql := 'select * from uploadtree where uploadtree_pk=' || UTpk; execute sql into UTrec; IF ((UTrec.ufile_mode (128)) = 0) THEN RETURN NEXT UTrec; END IF; UTpk := UTrec.parent; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; Merlin, I just reread what you wrote. ;-) Yes, your select * from (select * from your_func) would work. The function caller itself can sort the results (outside of postgres). I could also have a second function call the above, sorting the results. These just seem kludgy. That's why I was wondering if it were possible to select * from (select * from function_return_set) order by. But I see no way to reference the table to be returned. Thanks, Bob -- 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] Single character bitfields
Ben [EMAIL PROTECTED] writes: Out of curiosity, does postgres collapse multiple boolean columns to a bitfield internally? No. 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
Re: [GENERAL] Single character bitfields
On Thu, Aug 21, 2008 at 12:56 PM, Joshua Drake [EMAIL PROTECTED] wrote: On Thu, 21 Aug 2008 12:40:29 +1000 Andrew Maclean [EMAIL PROTECTED] wrote: I have a large database and I want to have several fields (among many) that are single character fields (in fact they are bitfields). 1) Is char(1) the most efficient way to store these fields? If not what is better? boolean? Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate I wouldn't be happy doing this because I prefer treating boolean data types as only TRUE or FALSE. Is char(1) one byte in size? -- ___ Andrew J. P. Maclean Centre for Autonomous Systems The Rose Street Building J04 The University of Sydney 2006 NSW AUSTRALIA Ph: +61 2 9351 3283 Fax: +61 2 9351 7474 URL: http://www.acfr.usyd.edu.au/ ___ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
