Re: [GENERAL] Build RPM from Postgres Source
Date: Fri, 12 Jul 2013 14:37:19 -0700 (PDT) From: ktewari1 cute.mee...@gmail.com To: pgsql-general@postgresql.org Subject: Build RPM from Postgres Source Message-ID: 1373665039319-5763633.p...@n5.nabble.com Hi, I need to have some different settings(like NAMEDATALEN etc.) and that's why I'm trying to build postgres from the source and to create an rpm to be send for install. Now, the build works fine but, I don't see a way to create an RPM. I'll greatly appreciate any help. Thanks, Kul You should take a look at mock, http://fedoraproject.org/wiki/Projects/Mock. I wrote up a short intro to this a little while ago. You can read it here: http://byrnejb.wordpress.com/2013/01/30/building-ruby-1-9-3-for-centos-6-3/ Although the example is not postgresql the process remains much the same on any RHEL derived distro regardless of the package. Good luck. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] Need help extripating plpgsql
On Thu, February 21, 2013 20:27, Adrian Klaver wrote: My previous not withstanding there is a reason I can see why this not so. Just because a user does not own an object does not mean they cannot use it. This allows a DBA to set up a template with a privilege scheme that suits their needs and then can be replicated. Under your proposal every time a database was created the privilege scheme would need to be reestablished. You want the one user model which can be had by doing everything as a superuser. This is why it is generally recommended to have various roles defined in your database cluster. One role being sufficiently privileged to do the superuser work and others for other tasks. It seems strange to me that a trusted extension, one that can be added by any database owner, is prevented from being treated as trusted in the default configuration. I have no opinion on whether or not plpgsql should be included by default in newly created databases but, I do object that it is included in such a way as to make its management by the subsequent database owner impossible. Lacking the expertise myself might I impose upon you to suggest what configuration of roles would permit the plpgsql extension to be owned by the database owner when added from a template? I am quite willing to use a template2 of my own devising to create new databases but I would rather not have to create a template for every user that might be granted the DBCREATE privilege. This an issue because each project requires at least two separate userids that require the DBCREATE role and both are used to automatically drop and create test and development databases as part of the testing arrangements specific to their project. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Need help extripating plpgsql
I am trying, without success, to create a PG-9.2 database without including the plpgsql extension. I have tried specifying template0 and the database is nonetheless created with plpgsql. I have deleted plpgsql from template1 and the new database is nonetheless created with plpgsql. I desire to remove plpgsql from newly created databases because the dump that is generated by pgdump contains this line: CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; And for some reason this statement causes my test suit to fail with this error: psql:/home/byrnejb/Projects/Software/theHeart/code/proforma_rails_3_2/db/structure.sql:22: ERROR: must be owner of extension plpgsql I do not understand why this happens since, as I read this, if the plpgsql extension already exists in the database, which it does, then this statement should not be executed at all. But it does. Further, I do not understand why or how plpgsql is being included into databases on create as I have removed it from template1 and it does not exist in template0 to begin with. I note that plpgsql has NO owner specified in the newly created database, which mystifies me as well. Naively I thought that newly created databases would have all of their components owned by the database owner. How do I resolve this problem? -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] Need help extripating plpgsql
On Thu, February 21, 2013 12:38, James B. Byrne wrote: I am trying, without success, to create a PG-9.2 database without including the plpgsql extension. I have tried specifying template0 and the database is nonetheless created with plpgsql. I have deleted plpgsql from template1 and the new database is nonetheless created with plpgsql. I desire to remove plpgsql from newly created databases because the dump that is generated by pgdump contains this line: CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; Wrong line. This is the line COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; And yes, I went through this a year ago with PG-9.1 and resolved it once by switching to template0 in the connection configuration. Now it is back with PG-9.2. using the exact same configuration code because evidently plpgsql is added regardless. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] Need help extripating plpgsql
On Thu, February 21, 2013 13:23, Merlin Moncure wrote: On Thu, Feb 21, 2013 at 11:48 AM, James B. Byrne byrn...@harte-lyne.ca wrote: On Thu, February 21, 2013 12:38, James B. Byrne wrote: I am trying, without success, to create a PG-9.2 database without including the plpgsql extension. I have tried specifying template0 and the database is nonetheless created with plpgsql. I have deleted plpgsql from template1 and the new database is nonetheless created with plpgsql. I desire to remove plpgsql from newly created databases because the dump that is generated by pgdump contains this line: CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; Wrong line. This is the line COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; And yes, I went through this a year ago with PG-9.1 and resolved it once by switching to template0 in the connection configuration. Now it is back with PG-9.2. using the exact same configuration code because evidently plpgsql is added regardless. curious why you want to do this. there was actually some debate back in the day about pros/cons of having pl/pgsql be a built-in feature, which as you can see is where things are going. I want to do this because my automated test harness is choking because it cannot add an absolutely worthless COMMENT to that extension. It cannot add the comment because the language extension is added to the database with an incorrect owner. A database created by userid X should, in ALL RESPECTS, be OWNED by userid X. When the ownership of database Y is changed from user A to user B then all of the attributes of database Y should become owned by B. For some reason this is not the case with the plpgsql language extension. if you don't mind surgery with a shotgun, you can simply drop the extension after the load resolves. I have tried this and it does not work. It does not work for the simple reason that the test harness recreates the test database from the dump file each run. The dump file is created with a COMMENT statement which cannot be applied to the plpgsql language extension statement unless the user that connects to the database is a superuser. That condition makes the granting of DBCREATE to another userid somewhat pointless. I have resolved this by: 1. as the postgres user creating a copy of template1 (template2) 2. as the postgres user assigning the test userid as owner of template2 3. as the postgres user dropping the plpgsql extension from template2 4. as the test user adding the plpgsql extension to template2 5. specifying template2 in the database connection configuration file. I believe, however, that this entire situation is a defect in postgresql-9.2 and 9.1. The plpgsql language extension should not be included in new databases if it does not already exist in the selected template or when no template is used at all. Surely the local DBA is the final arbiter of what a given installation wishes to have in their databases and forcing them to go through hoops to accomplish this is hardly user-friendly. Further, if a language, or for that matter any, extension is added to a new database from a template or other source then that extension should be owned by the owner of the resulting database and not by any other userid. If there is a good reason as to why this should be otherwise I would certainly like to have it explained to me. The current arrangement is not really satisfactory as it requires either separate template databases for each userid granted the DBCREATE role or the superuser role has to be granted in place of the DBCREATE role. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] Need help extripating plpgsql
On Thu, February 21, 2013 16:02, Adrian Klaver wrote: On 02/21/2013 12:14 PM, James B. Byrne wrote: The current arrangement is not really satisfactory as it requires either separate template databases for each userid granted the DBCREATE role or the superuser role has to be granted in place of the DBCREATE role. That is sort of the point of the template system, different templates for different situations. Creating a new database from the system provided standard templates is not what I would consider a different situation requiring a specialized template for each and every user granted the DBCREATE role. Requiring that seems to me to be busywork and a complete waste of DBA resources. If all the elements contained in the standard templates had their ownerships changed to that of the owner of the new database then my problem would never have arisen. I do not understand why this is not the case. Is there a reason why this is so? -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] Howto see template objects in PGAdmin3
On Thu, February 14, 2013 06:23, Guillaume Lelarge wrote: On Tue, 2013-02-12 at 15:32 -0500, James B. Byrne wrote: PG-9.2 PGAdmin3-1.16.0 I am attempting to do this from inside PGAdmin3 but I find that I no longer see either template0 or template1 in the object tree. Where are they found? You first need to check the Show System Objects in the treeview in the Options dialog. Then, they'll be available in the Databases node of the treeview. Thank you. With your help I now can view template1 in PGAdmin3. I notice that the extension in question, plpgsql, has no owner in template1 and yet I still receive this error when attempting to build a new test database from a pgdump: -- -- PostgreSQL database dump -- SET statement_timeout = 0; SET client_encoding = 'UTF8'; SET standard_conforming_strings = on; SET check_function_bodies = false; SET client_min_messages = warning; -- -- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: - -- CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; -- -- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: - -- COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; . . . ERROR: must be owner of extension plpgsql Is this a bug? -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Howto see template objects in PGAdmin3
PG-9.2 PGAdmin3-1.16.0 I have run into an old problem wherein the postgres user has ownership of the PSQL language extension in a newly created database. I wish to remove this extension from template1 as the database is automatically recreated on every test run and I am unable to specify template0 to the test software. I am attempting to do this from inside PGAdmin3 but I find that I no longer see either template0 or template1 in the object tree. Where are they found? -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] [SOLVED] Problems with a custom LOCALE
On Sun, December 16, 2012 15:13, Tom Lane wrote: James B. Byrne byrn...@harte-lyne.ca writes: PG::Error: ERROR: encoding UTF8 does not match locale en...@-mmm-dd.utf-8 DETAIL: The chosen LC_CTYPE setting requires encoding LATIN1. This is what I see on the host running postgresql-9.2 # LC_ALL=en_CA@-mmm-dd.utf8 locale charmap UTF-8 Running locale against the base en_CA@-mmm-dd on the PG host shows this. LC_ALL=en_CA@-mmm-dd locale charmap ISO-8859-1 You're showing us three different spellings of the locale name above. Are you really sure they're all equivalent? Beyond that, you probably need to find a locale guru. I see no reason to think there is anything wrong with the Postgres code for this, and every reason to think there's something wrong with your locale definition. But I don't know enough about custom locales to help you identify exactly what. regards, tom lane Well, I did eventually track this down and fix it. It was a number of issues which all sort of came together to drive me insane. First, the RDBMS was a remote host. On CentOS/RHEL ssh is configured to export the local session's LC environment to remote hosts. Problem, en_CA@-mmm-dd did not exist on the remote host. Running initdb in a ssh shell with the locale set to a non-existent local locale probably affected the database structure in some obscure way, although the postgresql.conf file said 'en_US.UTF-8'. However, perhaps the config file is ignored by the init.d script on RHEL in this respect as well. Of course, postgresql.conf does not yet exist when one runs initdb. Hmm. I will leave that one for the existentialists among us. In any case, installing the custom locale on the remote host did not solve the problem because when localedef could not find the UTF-8 character map it used ISO-8859-1 instead and kept that piece of information to itself because, I did not specify the -v option. Of course, if you do specify the -v option and there are any warnings then the existing character map is not replaced even when it does find the utf-8 character map. That is, unless you also specify the -c option. And finally, you need to archive the base character map (.utf8) and then create aliases for the rest (.utf-8, UTF8 and UTF-8). And the way one does that is to use the self-explanatory --no-archive option together with the -c option on the aliases but use --replace option on the localedef call to create the archived map. Really, localedef is perhaps the most Byzantine and under documented single function utility that I have run across since I stopped writing assembler. Anyway, after rebuilding the remote 9.2 database whilst having the remote session locale set to en_US.UTF-8, resetting the remote and local host's locale databases and aliases for en_CA@-mmm-dd, and then trying everything once more on the development host I ran into this error: PG::Error: ERROR: new encoding (UTF8) is incompatible with the encoding of the template database (LATIN1) HINT: Use the same encoding as in the template database, or use template0 as template. : CREATE DATABASE hll_th_forex_development ENCODING = 'UTF-8' So, I now must specify a template in the database connection, where I did not need to before. Furthermore, the template I must use is template0 because regardless of what locale initdb is run in template1 evidently is always created with LATIN1 encoding. At lease if there is a way to force template1 to utf8 during initdb I now lack the energy to discover it. However, specifying template0 in the db connection does allow the remote db creation to succeed. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] Problems with a custom LOCALE
On Fri, December 14, 2012 17:40, Tom Lane wrote: James B. Byrne byrn...@harte-lyne.ca writes: PG::Error: ERROR: encoding UTF8 does not match locale en...@-mmm-dd.utf-8 DETAIL: The chosen LC_CTYPE setting requires encoding LATIN1. : CREATE DATABASE hll_th_forex_test ENCODING = 'UTF8' TEMPLATE = template1 Sorta looks like you based that locale on an ISO 8859-1 locale, not a UTF8 locale. AFAICT from looking at the code, PG is seeing that nl_langinfo(CODESET) returns ISO-8859-1 or some variant spelling, so it complains. This is what I see on the host running postgresql-9.2 # LC_ALL=en_CA@-mmm-dd.utf8 locale charmap UTF-8 # However, on that system, because of PostgreSQL, we have left the locale unmodified from default provided in CentOS-5.8: # locale LANG=en_US.UTF-8 LC_CTYPE=en_US.UTF-8 LC_NUMERIC=en_US.UTF-8 LC_TIME=en_US.UTF-8 LC_COLLATE=en_US.UTF-8 LC_MONETARY=en_US.UTF-8 LC_MESSAGES=en_US.UTF-8 LC_PAPER=en_US.UTF-8 LC_NAME=en_US.UTF-8 LC_ADDRESS=en_US.UTF-8 LC_TELEPHONE=en_US.UTF-8 LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=en_US.UTF-8 LC_ALL= # locale charmap UTF-8 Running locale against the base en_CA@-mmm-dd on the PG host shows this. LC_ALL=en_CA@-mmm-dd locale charmap ISO-8859-1 On the calling system I see this: $ locale LANG=en_CA.UTF-8 LC_CTYPE=en_CA.UTF-8 LC_NUMERIC=en_CA.UTF-8 LC_TIME=en_CA.UTF-8 LC_COLLATE=en_CA.UTF-8 LC_MONETARY=en_CA.UTF-8 LC_MESSAGES=en_CA.UTF-8 LC_PAPER=en_CA.UTF-8 LC_NAME=en_CA.UTF-8 LC_ADDRESS=en_CA.UTF-8 LC_TELEPHONE=en_CA.UTF-8 LC_MEASUREMENT=en_CA.UTF-8 LC_IDENTIFICATION=en_CA.UTF-8 LC_ALL= $ locale charmap UTF-8 Previously we were running PG 8.4 on that host and did not encounter this problem. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Problem starting PG-9.2 on non-default port
I wish to run an instance of PG-9.2 concurrently with our production version (9.1). I have changed the listening port number. listen_addresses = 'pgsql-dbms.hamilton.harte-lyne.ca, localhost' #port = 5432# (change requires restart) port = 5433 However, I am getting this error when I try to start the new version while the old one is still running. Is there something else I must do to accomplish this? Why is the lock file using the default port number for this instance? cat ../pgstartup.log 2012-12-14 09:13:41 EST: LOG: XX000: could not bind IPv4 socket: Address already in use 2012-12-14 09:13:41 EST: HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. 2012-12-14 09:13:41 EST: LOCATION: StreamServerPort, pqcomm.c:438 2012-12-14 09:13:41 EST: WARNING: 01000: could not create listen socket for pgsql-dbms.hamilton.harte-lyne.ca 2012-12-14 09:13:41 EST: LOCATION: PostmasterMain, postmaster.c:892 2012-12-14 09:13:41 EST: FATAL: F0001: lock file /tmp/.s.PGSQL.5432.lock already exists 2012-12-14 09:13:41 EST: HINT: Is another postmaster (PID 1299) using socket file /tmp/.s.PGSQL.5432? 2012-12-14 09:13:41 EST: LOCATION: CreateLockFile, miscinit.c:822 -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem starting PG-9.2 on non-default port
On Fri, December 14, 2012 09:40, Adrian Klaver wrote: On 12/14/2012 06:26 AM, James B. Byrne wrote: I wish to run an instance of PG-9.2 concurrently with our production version (9.1). I have changed the listening port number. listen_addresses = 'pgsql-dbms.hamilton.harte-lyne.ca, localhost' #port = 5432 # (change requires restart) port = 5433 However, I am getting this error when I try to start the new version while the old one is still running. Is there something else I must do to accomplish this? Why is the lock file using the default port number for this instance? Are you sure you are starting the correct server instance? Well, this is what I am doing: service postgresql-9.2 start Starting postgresql-9.2 service: [FAILED] And this is the log file with the error: cat /var/lib/pgsql/9.2/pgstartup.log 2012-12-14 09:43:38 EST: LOG: XX000: could not bind IPv4 socket: Address already in use 2012-12-14 09:43:38 EST: HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. 2012-12-14 09:43:38 EST: LOCATION: StreamServerPort, pqcomm.c:438 2012-12-14 09:43:38 EST: WARNING: 01000: could not create listen socket for pgsql-dbms.hamilton.harte-lyne.ca 2012-12-14 09:43:38 EST: LOCATION: PostmasterMain, postmaster.c:892 2012-12-14 09:43:38 EST: FATAL: F0001: lock file /tmp/.s.PGSQL.5432.lock already exists 2012-12-14 09:43:38 EST: HINT: Is another postmaster (PID 1299) using socket file /tmp/.s.PGSQL.5432? 2012-12-14 09:43:38 EST: LOCATION: CreateLockFile, miscinit.c:822 And this is the running instance: service postgresql-9.1 status postgresql-9.1 (pid 1299) is running... So, I am fairly sure that I am trying to get 9.2 started and failing and not trying to start another instance of 9.1. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem starting PG-9.2 on non-default port
On Fri, December 14, 2012 09:52, Adrian Klaver wrote: Assuming an RPM install, see Devrims reply. Might one inquire as to why it is necessary to override the configuration file in the startup script? -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem starting PG-9.2 on non-default port
On Fri, December 14, 2012 10:21, James B. Byrne wrote: On Fri, December 14, 2012 09:52, Adrian Klaver wrote: Assuming an RPM install, see Devrims reply. Might one inquire as to why it is necessary to override the configuration file in the startup script? This is a patch for /etc/init.d/postgresql-9.2 to retrieve the port from the postgresql.conf file and override the default setting if it is set there. The last two calls to 'cut' handle both tabs and spaces as whitespace. 76a77,88 # Override port setting from postgresql.conf if set PGCONFPORT=$(grep -e ^port $PGDATA/postgresql.conf | \ cut -d = -f 2 | \ sed -e 's/^[ \t]*//' | \ cut -f1 | \ cut -d -f 1) if [[ -n $PGCONFPORT ]] # -n == defined and not blank then echo Setting PGPORT to $PGCONFPORT from $PGDATA/postgresql.conf PGPORT=$PGCONFPORT fi -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem starting PG-9.2 on non-default port
On Fri, December 14, 2012 10:21, James B. Byrne wrote: This is a patch for /etc/init.d/postgresql-9.2 to retrieve the port from the postgresql.conf file and override the default setting if it A slight, but important, correction that handles leading white space in the first selection: 76a77,88 # Override port setting from postgresql.conf if set PGCONFPORT=$(grep -e ^\s*port $PGDATA/postgresql.conf | \ cut -d = -f 2 | \ sed -e 's/^[ \t]*//' | \ cut -f1 | \ cut -d -f 1) if [[ -n $PGCONFPORT ]] # -n == defined and non-blank -z == not defined then echo Setting PGPORT to $PGCONFPORT from $PGDATA/postgresql.conf PGPORT=$PGCONFPORT fi -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Problems with a custom LOCALE
Having got the 9.2 instance running in parallel with the previous
version I am now encountering this difficulty:
Couldn't create database for {adapter=postgresql,
collate=en_US.UTF-8, ctype=en_US.UTF-8, encoding=UTF8,
database=hll_th_forex_development,
host=inet01.hamilton.harte-lyne.ca, pool=5,
password=hll_theheart_devl_password, sslmode=require,
template=template1, username=hll_theheart_db_devl}
PG::Error: ERROR: encoding UTF8 does not match locale
en...@-mmm-dd.utf-8
DETAIL: The chosen LC_CTYPE setting requires encoding LATIN1.
: CREATE DATABASE hll_th_forex_test ENCODING = 'UTF8' TEMPLATE =
template1
I am constructing this remotely using a Rake task borrowed from Ruby
on Rails 3.2.9 and employing ActiveRecord-3.2.9. We use a custom
local on all our servers en...@-mmm-dd.utf-8 which differs from
en_CA only in the presentation of the date. However, regardless of
the system setting, postgresql.conf was configured thus:
# These settings are initialized by initdb, but they can be changed.
lc_messages = 'en_US.UTF-8' # locale for system error message
# strings
lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
lc_numeric = 'en_US.UTF-8' # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for time formatting
So, what is going wrong here?
--
*** E-Mail is NOT a SECURE channel ***
James B. Byrnemailto:byrn...@harte-lyne.ca
Harte Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3
--
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] Having a problem with RoR-3.1.1 and Pg-9.1
On Tue, February 28, 2012 12:17, Adrian Klaver wrote: Alright here is what I found: template1=# \dL List of languages Name | Owner | Trusted -+--+- plpgsql | postgres | t template1=# CREATE DATABASE pl_test with owner=aklaver; CREATE DATABASE template1=# \c pl_test aklaver - You are now connected to database pl_test as user aklaver. pl_test= pl_test= \dL List of languages Name | Owner | Trusted -+--+- plpgsql | postgres | t (1 row) pl_test= \c - postgres You are now connected to database pl_test as user postgres. pl_test=# DROP EXTENSION plpgsql ; DROP EXTENSION pl_test=# \c - aklaver You are now connected to database pl_test as user aklaver. pl_test= CREATE EXTENSION plpgsql ; CREATE EXTENSION pl_test= \dL List of languages Name | Owner | Trusted -+-+- plpgsql | aklaver | t (1 row) So when you do the CREATE DATABASE it goes to template1 and grabs the PL with the permissions present in template1. If you want to have the language run with the permissions of the database owner you need to drop the superuser version(as the superuser) and then load it as the database owner. As to why: http://www.postgresql.org/docs/9.1/static/manage-ag-templatedbs.html I guess the options are either do as I did above or create a new template database as the owner you want and use that as the template for your CREATE DATABASE. Sigh. I will have to think on this before changing anything. To my mind, the most straight-forward way of dealing with this is to remove the language from template1 altogether. Thereafter, the db owner must explicitly add it back in where required. So, the default would be no extension. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] Having a problem with RoR-3.1.1 and Pg-9.1
On Tue, February 28, 2012 12:17, Adrian Klaver wrote: I guess the options are either do as I did above or create a new template database as the owner you want and use that as the template for your CREATE DATABASE. Why does this not work? = \c test You are now connected to database test as user devl. ALTER EXTENSION plpgsql OWNER TO devl; ERROR: syntax error at or near OWNER LINE 1: ALTER EXTENSION plpgsql OWNER TO devl; = \c - postgres You are now connected to database test as user postgres. ALTER EXTENSION plpgsql OWNER TO devl; ERROR: syntax error at or near OWNER LINE 1: ALTER EXTENSION plpgsql OWNER TO devl; -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] Having a problem with RoR-3.1.1 and Pg-9.1
On Tue, February 28, 2012 12:52, Adrian Klaver wrote: On 02/28/2012 09:50 AM, James B. Byrne wrote: Sigh. I will have to think on this before changing anything. To my mind, the most straight-forward way of dealing with this is to remove the language from template1 altogether. Thereafter, the db owner must explicitly add it back in where required. So, the default would be no extension. Well that is how it was prior to 9.0. It does not now appear to be the case. When I drop the test database and attempt to recreate it the plpgsql extension is automatically added back in with postgres as the owner. This was a new installation of 9.1 and I have made no modifications to the templates whatsoever. template1=# \dx List of installed extensions Name | Version | Schema | Description -+-++-- plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] Having a problem with RoR-3.1.1 and Pg-9.1
On Tue, February 28, 2012 13:28, Adrian Klaver wrote: Why does this not work? = \c test You are now connected to database test as user devl. ALTER EXTENSION plpgsql OWNER TO devl; ERROR: syntax error at or near OWNER LINE 1: ALTER EXTENSION plpgsql OWNER TO devl; = \c - postgres You are now connected to database test as user postgres. ALTER EXTENSION plpgsql OWNER TO devl; ERROR: syntax error at or near OWNER LINE 1: ALTER EXTENSION plpgsql OWNER TO devl; There is no OWNER clause in ALTER EXTENSION. If you are trying to do what I showed, you need to as the'postgres' user DROP EXTENSION plpgsl in database 'test' and then become owner 'dev1' and CREATE EXTENSION plpgsl And yet PGAdmin3-1.14.2 shows this in the sql pane when the plpgsql object is selected: - Extension: plpgsql -- DROP EXTENSION plpgsql; CREATE EXTENSION plpgsql SCHEMA pg_catalog VERSION 1.0; ALTER EXTENSION plpgsql OWNER TO postgres; It seems to me that this arrangement of including programming languages in a database is far more awkward than it needs be. Since ALTER EXTENSION is a PostgreSQL extension to SQL could not the ability to alter the owner of an extension be provided as well? As it presently stands, newly created databases automatically get plpgsql with its comment. The comment then causes any pg_restore file created with pg_dump to choke unless the script is run by the owner of the extension. Even if otherwise the user has CREATE DATABASE privileges. This behaviour effectively means that only the superuser can restore databases in 9.1 or build them from scripts; unless the default template is altered. Is this desired? What then does GRANT CREATE DATABASE mean in 9.1 then? It is certainly at odds with the behaviour in 8.x releases. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] Having a problem with RoR-3.1.1 and Pg-9.1
On Tue, February 28, 2012 14:03, Adrian Klaver wrote: The PgAdmin folks would be better able to help you with the exact reason for the above, but I suspect they really meant: http://www.postgresql.org/docs/9.1/interactive/sql-alterlanguage.html ALTER [ PROCEDURAL ] LANGUAGE name OWNER TO new_owner So in the category I have learned something new: template1=# \dL List of languages Name | Owner | Trusted -+--+- plpgsql | postgres | t (1 row) template1=# ALTER LANGUAGE plpgsql owner to aklaver; ALTER LANGUAGE template1=# \dL List of languages Name | Owner | Trusted -+-+- plpgsql | aklaver | t (1 row) I encountered a strange inconsistency with PGAdmin3-1.14.2 relating to this. After executing ALTER LANGUAGE plpgsql owner to devl; in the SQL query pane inside PGAdmin3 the extension ownership change is never reflected in the object properties pane. Even when disconnecting and reconnecting to the server after the change the properties pane of the extension in that database continues to show the plpgsql owner as postgres. psql on the other hand shows the new language owner as expected. By the way. Thank you very much for your assistance with this. It is greatly appreciated. Regards, -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] Having a problem with RoR-3.1.1 and Pg-9.1
On Tue, February 28, 2012 14:17, Adrian Klaver wrote: No, you just did not run into the issue, probably because your template1 was just a straight clone of template0 with no added features You are correct. It was the inability to change the comment on the extension as required by the pg_dump file because of the ownership of the extension which triggered this entire discussion. I have removed plpgsql from template1 on all the systems affected. The tests now run against 9.1 fine. Regards, -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Having a problem with RoR-3.1.1 and Pg-9.1
On: Fri, 24 Feb 2012 07:33:01 -0800, Adrian Klaver adrian.kla...@gmail.com wrote: On Friday, February 24, 2012 7:16:47 am James B. Byrne wrote: CentOS-5.7 RoR-3.1.1 Pg-9.1 I am trying to run a test suite against Pg-9.1 for a RoR-3.1.1 based application. When I run the test DB setup task it fails: $ rake db:test:prepare Using AdapterExtensions psql:/...rails3/db/development_structure.sql:22: ERROR: must be owner of extension plpgsql The offending line in development_structure.sql is: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; The error does not happen if the target DBMS is Pg-8.4. I recall that with 8.4 that we would see warnings relating to trying to load/create plpgsql when it already existed in the test database but now we are getting an error which stops further processing. Is there a way around this? See here for similar report: http://archives.postgresql.org/pgsql-general/2012-02/msg00504.php The options seem to be run the script as the owner of the plpgsql EXTENSION or do not include the comment. How does one instruct pg_dump not to include the COMMENT for the plpgsql extension? The case in question is the automated creation of an sql script to create a test database from a development database. The owners of the development and test databases are the same but this user id is not a superuser and it certainly does not own the plpgsql extension. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] Having a problem with RoR-3.1.1 and Pg-9.1
On Mon, February 27, 2012 13:54, Adrian Klaver wrote: On 02/27/2012 08:51 AM, James B. Byrne wrote: The options seem to be run the script as the owner of the plpgsql EXTENSION or do not include the comment. How does one instruct pg_dump not to include the COMMENT for the plpgsql extension? I am not sure pg_dump is including the COMMENT. From your previous post the source of the COMMENT would seem to be development_structure.sql, which I assume is coming from RoR. RoR is using pg_dump to create development_structure.sql. A direct invocation of pg_dump -s from the command line gives this output. . . . COMMENT ON SCHEMA public IS 'standard public schema'; -- -- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: -- CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; -- -- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: -- COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; . . . So, RoR is simply trying to deal with what Pg is providing it. The questions are: 1. Can the comments be suppressed? If so then how are the COMMENT statements suppressed within Pg itself or pg_dump?. The development_structure.sql is regenerated automatically so editing that file is futile. 2. Why is this an error in the first place? Surely a warning is sufficient if a comment cannot be applied. 3. Why are these dependencies not owned by the database owner to begin with? Surely this code: CREATE EXTENSION plpgsql SCHEMA pg_catalog VERSION 1.0; ALTER EXTENSION plpgsql OWNER TO postgres; could just as easily specify the actual database owner instead? -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] Having a problem with RoR-3.1.1 and Pg-9.1
On Mon, February 27, 2012 14:30, Adrian Klaver wrote: On 02/27/2012 08:51 AM, James B. Byrne wrote: The options seem to be run the script as the owner of the plpgsql EXTENSION or do not include the comment. How does one instruct pg_dump not to include the COMMENT for the plpgsql extension? Did some testing. So when you use 9.1 pg_dump to dump from versions prior to 9.1 it pulls a language in using CREATE OR REPLACE langname with no COMMENT on the language. If you use 9.1 pg_dump to dump a 9.1 database is uses the CREATE EXTENSION langname and adds the COMMENT you are seeing. As I know of no way to disable COMMENTs in a dump you are left with my suggestion from previous post, of running script as superuser. Then what is the value of the GRANT CREATE DATABASE TO userid ability if a user cannot create a database without superuser privileges? -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] Having a problem with RoR-3.1.1 and Pg-9.1
On Mon, February 27, 2012 15:44, Tom Lane wrote: James B. Byrne byrn...@harte-lyne.ca writes: 1. Can the comments be suppressed? No. 2. Why is this an error in the first place? Because you're not running the script as superuser. regards, tom lane Why is it necessary to run a script that creates a database by the superuser when the CREATE DATABASE privilege has been granted to the use running the script? This was not previously the case. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] Having a problem with RoR-3.1.1 and Pg-9.1
Here is an interesting situation. In PGAdmin3-1.14.2 when I display the extension properties then I see this: CREATE EXTENSION plpgsql SCHEMA pg_catalog VERSION 1.0; ALTER EXTENSION plpgsql OWNER TO postgres; However, if I do this exact statement in the SQL pane while connected as the postgres superuser then I see this: ALTER EXTENSION plpgsql OWNER TO postgres; ERROR: syntax error at or near OWNER LINE 1: ALTER EXTENSION plpgsql OWNER TO postgres; ^ ** Error ** ERROR: syntax error at or near OWNER SQL state: 42601 Character: 25 So, as I am unfamiliar with the EXTENSION syntax, what is wrong with the preceding statement? What is the correct syntax? -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] Having a problem with RoR-3.1.1 and Pg-9.1
Obviously, I am missing something important here. The database in question is created thusly: CREATE DATABASE test WITH OWNER = devl ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' CONNECTION LIMIT = -1; The manual says this: Loading an extension requires the same privileges that would be required to create its component objects. For most extensions this means superuser or database owner privileges are needed. The user who runs CREATE EXTENSION becomes the owner of the extension for purposes of later privilege checks, as well as the owner of any objects created by the extension's script. However, looking at the extension properties shows this: -- Extension: plpgsql -- DROP EXTENSION plpgsql; CREATE EXTENSION plpgsql SCHEMA pg_catalog VERSION 1.0; ALTER EXTENSION plpgsql OWNER TO postgres; So, why is the plpgsql extension in test owned by postgres and not by devl to begin with? -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] Having a problem with RoR-3.1.1 and Pg-9.1
On Mon, February 27, 2012 16:37, Adrian Klaver wrote: On Monday, February 27, 2012 1:23:22 pm James B. Byrne wrote: Obviously, I am missing something important here. The database in question is created thusly: CREATE DATABASE test WITH OWNER = devl ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' CONNECTION LIMIT = -1; When you run the above are you doing it as user dev1 or the postgres user? The manual says this: Loading an extension requires the same privileges that would be required to create its component objects. For most extensions this means superuser or database owner privileges are needed. The user who runs CREATE EXTENSION becomes the owner of the extension for purposes of later privilege checks, as well as the owner of any objects created by the extension's script. However, looking at the extension properties shows this: -- Extension: plpgsql -- DROP EXTENSION plpgsql; CREATE EXTENSION plpgsql SCHEMA pg_catalog VERSION 1.0; ALTER EXTENSION plpgsql OWNER TO postgres; So, why is the plpgsql extension in test owned by postgres and not by devl to begin with? Is plpgsql in template1 and you are running the CREATE DATABASE script as postgres? If template1 has plpgsql as a language extension by default then I have not removed it. If it does not then I have not added it either. It is likely that I created the database initially in PGAdmin3 while connected to the server as the postgres user. Why would creating a database with a specified owner result in critical bits of it not being owned by the specified owner? That strikes me as bizarre. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] Having a problem with RoR-3.1.1 and Pg-9.1
On Mon, February 27, 2012 17:16, Adrian Klaver wrote: From psql do \l and see who actually owns the database. List of databases Name|Owner | Encoding | ---+--+--+-- devl | devl | UTF8 | test | devl | UTF8 | postgres | postgres | UTF8 | template0 | postgres | UTF8 | | | | template1 | postgres | UTF8 | | | | (5 rows) Collation, C-Type and Access Privileges columns removed. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Having a problem with RoR-3.1.1 and Pg-0.1
CentOS-5.7 RoR-3.1.1 Pg-9.1 I am trying to run a test suite against Pg-9.1 for a RoR-3.1.1 based application. When I run the test DB setup task it fails: $ rake db:test:prepare Using AdapterExtensions psql:/...rails3/db/development_structure.sql:22: ERROR: must be owner of extension plpgsql The offending line in development_structure.sql is: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; The error does not happen if the target DBMS is Pg-8.4. I recall that with 8.4 that we would see warnings relating to trying to load/create plpgsql when it already existed in the test database but now we are getting an error which stops further processing. Is there a way around this? I have already reported the problem to the Rails list in order to discover if this is a defect or if there is some way to avoid this issue with Pg-9.1. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PGAdmin3 for CentOS-5_186_64 and pgdg91
I need a version of pgadmin4 which can handle pg-9.1 and which runs on RHEL5_186_64 architectures. I have installed the pgdg-91-centos.repo yum repo file from pgdgrpm.org. However: yum whatprovides */pgadmin3 --enablerepo=pgdg91 does not return anything. If I do exactly the same thing on a Centos5_i686 box then I see this instead: pgadmin3_91-1.14.0-3.rhel5.i386 : Graphical client for PostgreSQL Repo: pgdg91 Matched from: Filename: /usr/pgsql-9.1/bin/pgadmin3 Filename: /usr/pgsql-9.1/share/pgadmin3 Does anyone reading this message know where I can get pgadmin3_91-1.14.0-3.rhel5.i86_64 or why it is not available from pgdgrpm.org? Regards, -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] Mixed Pg-9.1.2 and 8.4.4 environment
On Tue, January 10, 2012 09:28, Adrian Klaver wrote: On Tuesday, January 10, 2012 6:17:27 am James B. Byrne wrote: My question: Is their a way to specify a pg_dump option to retain compatibility with a lower version pg database? Alternatively, can one install a higher level pg_dump in an otherwise 8.4.4 installation? Short answer is no. The reason is seen in the errors below. EXTENSION does not exist in Postgres below 9.1. In this particular case the EXTENSION is being used to load a procedural language and that had a pre 9.1 analog in CREATE LANGUAGE. In other instances that would not be the case. pg_dump can dump forward i.e use new pg_dump version to pull data forward from old Postgres version, but not the other way around. We upgraded the backup systems to v.9.1.2, which was fairly trivial given that they simply load pgdumps to begin with. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Mixed Pg-9.1.2 and 8.4.4 environment
We are in the process of migrating our various servers to a kvm host with guest vms. The new guests run pg-9.1.2 under CentOS-6.2. However, our off site warm spares are still running pg-8.4.4 under CentOS-5.7. We have an automated transfer routine that dumps the live database, now running v.9.1.2, and sends the dump file to the off site host. There the dump is restored to an 8.4.4 version. My question: Is their a way to specify a pg_dump option to retain compatibility with a lower version pg database? Alternatively, can one install a higher level pg_dump in an otherwise 8.4.4 installation? The reason for the question is that we are receiving these warnings: pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 257; 3079 12506 EXTENSION plpgsql pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at or near EXTENSION LINE 1: DROP EXTENSION plpgsql; ^ Command was: DROP EXTENSION plpgsql; pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at or near EXTENSION LINE 1: CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalo... ^ Command was: CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; pg_restore: [archiver (db)] Error from TOC entry 3473; 0 0 COMMENT EXTENSION plpgsql pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at or near EXTENSION LINE 1: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural languag... ^ Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; pg_restore: [archiver (db)] Error from TOC entry 3462; 0 20928 TABLE DATA wiki_content_versions hll_redmine_db_admin pg_restore: [archiver (db)] COPY failed: ERROR: invalid input syntax for type bytea CONTEXT: COPY wiki_content_versions, line 1, column data: \x68312e205b5b57696b6953746172747c48617274652026204c796e65204c696d697465642053746172745d5d0d0a0d0a0d... WARNING: errors ignored on restore: 4 -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PGAdmin3 for 9.1 and CentOS-5_x86_64
I cannot seem to find a copy of pgadmin3 built for 9.1 on the x86_64 platform for the CentOS-5 (RHEL5) distro. I can find a pgadmin3 v1.14.1 rpm package for rhel6.x86_64 but none for rhel5.x86_64. I sent a message off to the maintainer last week but with the holidays no doubt it is sitting in his inbox. Does anyone here know if there is reason why CentOS-5 has the i386 package built for pgadmin3 v1.14.1 but not the x86_64? Or does it exist and I am simply not finding it? -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL server architecture
We run a small in-house data centre for our various operations. Currently, we are moving applications from dedicated boxes to kvm based CentOS-6.1 virtual machines on a single CentOS-6.1 host. At issue is the question on how to handle the PostgreSQL instances that we currently have running on different machines. As we see it presently we have the option of having one VM host a centralized PostgreSQL server with multiple databases or continue with each application specific VM running its own copy of PostgreSQL with just the dedicated application database. Since whatever we chose we are likely to be using five years from now I am soliciting informed option over which option is considered a better choice for the long term. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] OT DBA type question - GRANT PRIVILEGE
On Thu, December 8, 2011 20:23, Craig Ringer wrote: On Thu, December 8, 2011 17:28, Andy Colson wrote: These are the only replies I received. Am I to conclude that most of the people on the list do not use GRANT PRIVILEGE to implement anything more than the minimal authorization scheme required to obtain access for anyone? Nil reports, as in: Generally, we do not use PostgreSQL's GRANT PRIVILEGE to implement detailed user ACLs, would be most welcome. If instead you are using GRANT PRIVILEGE and have not responded, a simple We (often, occasionally, seldom) use detailed GRANT PRIVILEGE based user ACLs is sufficient. I really want to get a sense of how prevalent using GRANT PRIVILEGE, beyond the minimum required, is. And this seems like a very good place to discover it. Again, please reply off-list if you wish. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] OT DBA type question - GRANT PRIVILEGE
On Fri, December 9, 2011 12:13, James B. Byrne wrote: Just to clarify the question. What I am asking basically comes down to if separate user ids are added for most individuals that access the database or not. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] OT DBA type question - GRANT PRIVILEDGE
Do many of you implement fine grained security provisions at the DBMS level using GRANT PRIVILEGE ON object TO user/role? My background with DBMS security is mostly in CODASYL shallow network systems where user level access control to datasets and fields was mainly a matter for the application and / or operating system. Therefor the number of user ids known to the DBMS was minimal. The topic of RDBMS security has arisen in a discussion and, lacking evidence of my own, I am curious to discover just how frequently DBMS userids tied to specific individuals are used in production RDBMS based systems. I am also curious to know how often VIEWS are tied to individual user IDs known to the DBMS rather than to shared user IDs known only to an application through a configuration file. This is not really a PG specific question so if anyone wishes to reply privately rather than to the list that is fine with me. Thanks, -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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: [pgsql-general] Need Help With a A Simple Query That's Not So Simple
Date: Mon, 31 Oct 2011 16:51:02 -0600 From: Bill Thoen bth...@gisnet.com To: Postgrresql pgsql-general@postgresql.org Subject: Need Help With a A Simple Query That's Not So Simple Message-ID: 4eaf2656.6020...@gisnet.com I think this should be easy, but I can't seem to put the SQL together correctly and would appreciate any help. (I'm using Pg 8.4 in CentOS 5.5, if that matters.) I have a table of Farms and a table of crops in a 1:M relationship of Farms : Crops. There are lots of different crops to choose form but for now I'm only interested in two crops; corn and soybeans. Some farms grow only corn and some grow only soybeans, and some grow both. What I'd like to know is, which Farms and how many are growing only corn, which and how many are growing soybeans and which and how many are growing both? I can easily get all the corn growers with: SELECT a.* FROM farms a JOIN crops b ON a.farm_id=b.farm_id WHERE crop_cd='0041' I can do the same with soybeans (crop_cd= '0081') and then I could subtract the sum of these from the total of all farms that grow either corn or soybeans to get the number of farms growing both, but having to do all those queries sounds very time consuming and inefficient. Is there a better way to get the farm counts or data by categories like farms growing only corn, farms growing only soybeans, farms growing both? I'm also interested in possibly expanding to a general case where I could select more than two crops. and get counts of the permutations. Here's a sketch of the relevant pieces of the data base. *Tables:* farms crops === === farm_id bigint (pkey) crop_id (pkey) type farm_idforeign key to farms size crop_cd0041 = corn 0081=soybeans ...year ... Any help would be much appreciated. TIA, - Bill Thoen I believe that what you are trying to do is called relational algebra division. Take a look at these references and see if either fits your needs: http://en.wikipedia.org/wiki/Relational_algebra#Division_.28.C3.B7.29 http://www.cs.arizona.edu/~mccann/research/divpresentation.pdf -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] Error Importing CSV File
On: Fri, 15 Jul 2011 13:57:03 -0400, Bryan Nelson sh...@shreks-place.com Tom, rake is a rails command, also after doing a \d geo_data it does show that it's adding an id column before everything else. I'm guessing my best bet is going to be creating the table by hand as I have no idea how to tell it not to create the extra field. Thanks for the help everyone, figured it had to be something simple. Shows how new I am at postgres. 1. Rake has nothing to do with Ruby on Rails. Rake is the Ruby Make utility. I believe that it and RubyGems, the Ruby package manager, are now bundled with Ruby-1.9 but they are still separate projects and utilities. The RoR framework is a completely different beast. 2. If you are using rake db:migrate then you are using ActiveModel/ActiveRecord from Rails which provides the task that you are running with Rake. Removing the synthetic key (:id) that AR expects from your tables is going to cause you a world of hurt if you then expect to access your database using ActiveRecord. 3. I suggest that you obtain the use of a copy of The Rails Way by Obie Fernandez or either Ruby for Rails or The Well-Grounder Rubist by David A. Black before you go much further into your project. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Infinity dates in RoR was How to handle bogus nulls from ActiveRecord
On Fri, May 13, 2011 13:04, James B. Byrne wrote: I have opened an issue for this with the ActiveRecord folks. https://github.com/rails/rails/issues/544 This has been addressed by the AR team and is committed to master. +-Infinity support for dates is slated for general release with RoR-3.0.8. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] How to handle bogus nulls from ActiveRecord
On Thu, May 12, 2011 20:30, Eric Hu wrote: David suggested using a guesstimate default date along with a boolean to indicate when you're using guesstimates. I think this is a solid approach, but if the default expected_by idea doesn't work for you, a boolean would still make this a lot easier on the Rails side. Since this part of the project is still in development, albeit partially in use for some purposes, all those approaches are under cosnideration considered. In fact some things are implemented in that fashion for other parts of the system, particularly with respect to current status. However, the main problem to be solved is this issue over the default values for time stamp columns, all of which must have a NOT NULL constraint since we simply cannot allow an errant application overwrite valid data, as would have happened in the case under consideration. I am coming to the conclusion that an arbitrary value of 1231 is a better approach than using PGs built-in idea of 'infinity' since I was told that concept is not supported in Ruby. Actually, it turn out that 'infinity' is supported in Ruby. Apparently infinity can be represented by assigning the value obtained by dividing a float by zero. $ irb ruby-1.8.7-p334 :001 infinity = 1.0/0 = Infinity ruby-1.8.7-p334 :002 ninfinity = -1.0/0 = -Infinity ruby-1.8.7-p334 :003 So, I guess this now qualifies as a bug in the Ruby pg adapter gem. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] How to handle bogus nulls from ActiveRecord
On Fri, May 13, 2011 11:50, Dickson S. Guedes wrote: Well, fetching from database it came nil and when saved into, it was trying to save a serialized object. From postgresql_adapter.rb [1] you can see that it returns the correct internal type based when field type is datetime, but i can't see the same thing for 'date', i suppose that it going [2] to 'super' [3], so I suppose that this method [4] should be override in 'postgresql_adapter.rb'. I have opened an issue for this with the ActiveRecord folks. https://github.com/rails/rails/issues/544 -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to handle bogus nulls from ActiveRecord
It is required for application data verification filters that default values for table columns are known to ActiveRecord when creating a new row. So ActiveRecord obtains the default values from the tables dynamically and assigns them to their appropriate column attributes. The problem we encounter arises because ActiveRecord then uses those column assignments when inserting a row even if the column is not otherwise referenced. I am developing a web application using the Ruby on Rails framework with PostgreSQL as the back-end store. In one of our tables we have a column called expected_by which is a time-stamp. It is set to NOT NULL DEFAULT 'INFINITY'. However, Ruby has no concept of infinity and whatever the PostgreSQL adapter is returning for it ActiveRecord receives as nil which is converted to NULL. So, the real fix to this is to alter the persistence class so that columns with default values are not explicitly set to those values on insert. This is unlikely to happen in the short term and will take some time to be integrated into the framework even when it is completed, if ever, So solve this for the moment what I think I require is a trigger on expected_at which tests for NULL on insert and converts it to infinity. The other alternative is to simply set the default to some valid, but unreachable, date like -12-31. I would like other opinions about how to best handle this situation and observations on what other significant concerns I may not be aware of but should provide for. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] How to handle bogus nulls from ActiveRecord
On Thu, May 12, 2011 12:40, David Johnston wrote: Not a huge fan of Infinity as a value...but that just may be lack of experience. I'd probably remove the NOT NULL constraint on expected_at and deal with tri-value logic; or also include a boolean (is_expected) and form queries like Well, actually, the reason for the NOT NULL constraint is to catch application errors exactly like this one. Removing it is not contemplated. I had no idea that AR actually 'copied' and used default values on columns that were not referenced in the application code until I encountered this. And had it gone undetected this would have been a major problem later on. As it was, our tests brought it to our attention quite early which is why we can contemplate several solutions. Without more info as to how you use expected_at other advice is difficult but can you user a meaningful value (say now()+'30 days'::interval) for the default? The column expected_by contains an estimated time of arrival for a particular conveyance. When a row is initialized this value is unknown some of the time. The expected_by value is reset to the arrived_at value on UPDATE if and only if expected_by is greater than arrived_at. Conveyances that have +infinite expected_by time-stamps are considered pending. At some point conveyance rows that are never going to arrive are otherwise flagged. On the other hand, rows with overdue expected_by values are given somewhat more attention, to put it mildly. So, we either fix the problem with AR, possibly by moving to Sequel ORM for this case, although I have not yet received an answer as to whether it does any better; Or we trap and override NULL values with infinity in a trigger; Or we choose for the default value a fixed date far, far into the future. +Infinity was chosen as a default to avoid the complexities of dealing with NULL logic in SELECTS. I suppose that the simplest solution is to go with a date of -12-31 and treat that value like infinity. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] How to handle bogus nulls from ActiveRecord
On Thu, May 12, 2011 15:51, David Johnston wrote: +Infinity was chosen as a default to avoid the complexities of dealing with NULL logic in SELECTS. I suppose that the simplest solution is to go with a date of -12-31 and treat that value like infinity. The just make it work solution has many merits - I would also probably just use -12-31 as a close approximation for +infinity; which itself is just there because you are avoiding estimate is unknown. Why bother updating the expected_by value once the conveyance is no longer pending? Do you not really care if something arrived early? Even if you do not currently it seems a waste to throw out the data when you can readily get the same result as-needed (CASE WHEN expected_by = arrived_at THEN arrived_at ELSE expected_by END) without giving up the ability to calculate The main reason to update expected_by is that sometimes the conveyance arrives without the expected_by ever being set. Leaving the expected_by value at infinity, or 1231, or NULL, complicates other parts of the system. However, leaving untouched expected_by values that are less than the infinite value is doable and is a better approach. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Passing infinity as a timestamp value in Ruby
I have run into a situation in Ruby on Rails-3.0.5 wherein the framework attempts to set unreferenced columns to their default values as extracted from the DBMS. However, it cannot handle infinity as a datetime class, which evidently is what AR maps timestamps to, and so attempts to insert NULL instead. As there is a NOT NULL constraint on that column the INSERT always fails. How would one pass a value of 'infinity' to PostgreSQL in this case. Am I constrained to hand craft an SQL INSERT query? I have raised the issue on the Rails Core list but if a solution to this problem is known to anyone here then I would like to learn of it. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] NULL value vs. DEFAULT value.
version = postgresql-8.4.4-2PGDG.el5.src.rpm I am seeing this problem and I cannot explain why it is happening. Evidently I misapprehend something about the interaction of NOT NULL and DEFAULT. If someone could tell me what the actual case is I would appreciate it very much. The table definition looks like this: CREATE TABLE ca_customs_shipments ( id integer NOT NULL, . . . weight_mass_gross numeric(14,4) DEFAULT 0.0 NOT NULL, weight_mass_gross_uom character varying(3) DEFAULT ' '::character varying NOT NULL, weight_mass_net numeric(14,4) DEFAULT 0 NOT NULL, weight_mass_net_uom character varying(3) DEFAULT ' '::character varying NOT NULL, . . . ); My question is: Why am I getting a NULL exception? Should I only specify DEFAULT and drop the NOT NULL constraint? -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] NULL value vs. DEFAULT value.
On Tue, March 8, 2011 10:09, Scott Ribe wrote: On Mar 8, 2011, at 7:54 AM, James B. Byrne wrote: My question is: Why am I getting a NULL exception? Because you're trying to insert NULL explicitly? Yes, that is the problem. Evidently RoR's ActiveRecord helpfully converts a string containing nought but spaces to nil when a numeric value is required for the column type. The problem arises with a single unit record received from the government system that has a UOM code provided but the associated decimal value field is blank. Since the default is zero in our DB I have altered our load program to coerce a value of zero for strings containing only spaces destined for numeric columns. But, it feels ugly. I would really like to be able to coerce nils to some value on a column by column basis on the DBMS side. This is not really a DEFAULT value and I do not know what I would call it if such a thing did exist. I suppose a trigger and function is called for. Thanks for the help. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] Screencasts for PostgreSQL
On Thu, March 3, 2011 09:18, Willy-Bas Loos wrote: maybe this? http://enterprisedb.com/resources-community/webcasts-podcasts-videos http://enterprisedb.com/resources-community/webcasts-podcasts-videos cheers, Thanks for the tip. I am taking a browse through these. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to approach dynamic status reporting
I have a situation whereby edi unit record files from an external system are read, parsed and loaded into a PostgreSQL database. As transmissions relating to each transaction are read a log table entry is made by transaction for each type of transmission encountered. The nature of the external application is such that the current status of any given transaction is ultimately dependent upon the contents of the log table entries associated with that transaction. For example: Given log entries for transaction X of aa99, bb88, cc77, the current status of X might be: 'completed'. However the status of X derived from log entries of aa99, bb88, cc77, dd66 might be: 'under revision', while that from log entries aa99, bb88, cc77, dd66, aa99 might be: 'in progress'. I have that part implemented and, insofar as testing reveals, working. My problem is that I now wish to select transactions from the parent table based upon their derived status values. I can see several ways to proceed. For instance I could store the last calculated status value as a column on the parent table and then use a WHERE table.column IN selection. First, however, I wish to inquire if this sort of thing crops up elsewhere and, if so, how is it handled? Sincerely, -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Screencasts for PostgreSQL
I recently viewed a screen-cast on PostgreSQL developed by Peepcode.com and obtained a few really valuable insights respecting full text searches. These were things that I was dimly aware of but that extensive reading had not revealed to me ( lacking as I am in the imagination necessary ). I was wondering if any here know of similar presentations on PostgreSQL usage and administration that might be available to me. Free is good but I am willing to pay a reasonable fee for such things as I did for the material from Peepcode. Any suggestions? -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Getting a sample data set.
I am working with Ruby on Rails and I have stumbled into a situation which turned out to be, surprisingly for me, somewhat involved. Given a table shipments having a column called mode I want to extract one entire shipment row (all columns) for each distinct value of mode. Assuming that there are 1700 rows and that there are just five distinct values in use for mode then I want to return five rows with all their columns and each one having a different value for mode. If I use the distinct clause then I only return the rows making up the distinct clause. Employing this approach produces either many more matches than I want or only returns the mode column. While I could not accomplish this with a single ORM call to ActiveRecord I solved this using an iterator inside RoR. My programmatic solution was: x = Shipment.select(DISTINCT(mode)) ms = [] x.each do |s| ms Shipment.find_by_mode(s.mode) end Which gives me a collection of rows each having a different mode. But now I am curious how this is done in plain SQL. I have have not found any useful guide as to how to approach this problem in the reference materials I have to hand. I cannot believe that I am the first person to require this sort of thing of SQL so if anyone can point me to a reference that explicitly sets out how to accomplish this I would greatly appreciate it. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] Getting a sample data set.
On Tue, January 18, 2011 13:23, Alban Hertroys wrote: Standard SQL alternatives tend to get complex, using self-joins to weed out all the records you don't want (the exact term for such joins escapes me right now, that would help with Googling if you're looking for examples). Would the term be a grouped self join? Basically you do something like: SELECT s1.mode FROM shipments AS s1 WHERE NOT EXISTS ( SELECT NULL FROM shipments AS s2 WHERE s1.mode = s2.mode AND s1.somecolumn s2.somecolumn ) I can see the motivation for something like DISTINCT ON. I take it that this syntax is peculiar to PostgreSQL?: -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] Getting a sample data set.
On Tue, January 18, 2011 14:28, Alban Hertroys wrote: Nope, but some Googling put me on the right track. It's called a correlated subquery. Thank you for this. I will delve further. I can see the motivation for something like DISTINCT ON. I take it that this syntax is peculiar to PostgreSQL?: I suppose you meant particular? Yes, definitely. Although I'm sure some would find it peculiar as well :) No. I meant peculiar. As in characteristic of only one person, group, or thing; distinctive . . . to PostgreSQL Regards, -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Ad hoc report writer
I have occasion to produce reports from our PostgreSQL database which are ephemeral. In a previous life on CODASYL installation I used a report writer called QUIZ to dash these things off. Is there anything of a similar nature in the FOSS community that people care to recommend for use with PostgreSQL? -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Set new owner on cloned database
I am testing a Rails deployment and wish to copy a database assigning it an new owner. I have tried this: createdb --owner=hll_theheart_db_devl --template=hll_th_deploytest_prod hll_theheart_devl While this indeed sets the database owner to hll_theheart_db_devl everything else, schema, tables whatever, remains owned by the original owner. Is there no way to change the owner everywhere in the cloned database using cretedb? Or am I constrained to do a dump all and restore? -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] Set new owner on cloned database
On Wed, December 8, 2010 17:46, Guillaume Lelarge wrote: You should try REASSIGN OWNED BY. See http://www.postgresql.org/docs/9.0/interactive/sql-reassign-owned.html Thanks for that. I ended up doing a pg_dump followed by a sed followed by a psql which sufficed for my purposes, even it it did seem a bit convoluted. The REASSIGN OWNED BY seems the more sensible approach. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PG84 and SSL on CentOS-5.5 was PG84 and SELinux
I received absolutely no reply to my question on the CentOS mailing list so I have to turn to this venue again for help. I note the following things: postgresql-server.i386 8.4.4-2PGDG.el5 installed openssl.i686 0.9.8e-12.el5_4.6 installed Might there be a problem between the server being compiled for i386 and openssl for i686? I cannot for the life of me determine what configuration problem causes this error. On Fri, December 3, 2010 16:04, James B. Byrne wrote: When I try to start the server with ssl=on it fails with this error: Auto configuration failed 29006:error:0E065068:configuration file routines:STR_COPY:variable has no value:conf_def.c:629:line 207 -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] PG84 and SSL on CentOS-5.5 was PG84 and SELinux
On Tue, December 7, 2010 16:56, Joshua D. Drake wrote: No those lib differences are both still 32bit. You would have a problem if one was 64bit. So you should be fine there. Joshua D. Drake Ok. How do I get postgresql to cough up more processing detail on startup? The message that I presently get makes no sense at all to me. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] PG84 and SSL on CentOS-5.5 was PG84 and SELinux
I have now tracked down and resolved the problem. There were clues to the solution in the error message but I lacked sufficient experience with ssl to realize it. The error was an uncommented line in /etc/pki/tls/openssl.cnf that depended upon an environment variable (ALTNAME) being set (subjectAltName=$ENV::ALTNAME). This was line 270 in that file. Note the error message: Auto configuration failed 29006:error:0E065068:configuration file routines:STR_COPY:variable has no value:conf_def.c:629:line 207 Given what I know now I infer that conf_def is the variable that holds the actual file name of whatever configuration file is passed to openssl. The error message would have been far more informative had it provided the variable value rather than the variable name. And, I have no idea why PG84 choked on this and PG81 did not. Anyway, our upgraded PG84 service is now running with ssl enabled. Many thanks for the hints and suggestions. They did in fact eventually point me in the right direction. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] PG84 and SELinux
On Mon, December 6, 2010 00:47, Greg Smith wrote: That looks to be the str_copy routine from conf_def.c in the OpenSSL code, i.e. line 624 of the version at: http://code.google.com/p/commitmonitor/source/browse/trunk/common/openssl/crypto/conf/conf_def.c So guessing something in the SSL autonegotiation is failing here in a really unexpected way. The problem was an expired pki certificate. When we first used ssl for pg we did not have our private CA set up. So we generated a self-signed certificate. That certificate expired this past July and I infer that while 8.1 did not care 8.4 evidently does. In any case, we generated a new key and had a certificate signing request signed by our CA. We installed both as server.key and server.crt in the pgsql/data directory with chmod 600 and chown postgres:postgres. Setting the postgresql.conf ssl option to on and restarting the server no longer causes any error. Than you all for the help. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] PG84 and SELinux
On Mon, December 6, 2010 13:29, James B. Byrne wrote: The problem was an expired pki certificate. When we first used ssl for pg we did not have our private CA set up. So we generated a self-signed certificate. That certificate expired this past July and I infer that while 8.1 did not care 8.4 evidently does. Wrong again. I misinterpreted what I was seeing. Even with the new cert and key I get the same error. Auto configuration failed 16276:error:0E065068:configuration file routines:STR_COPY:variable has no value:conf_def.c:629:line 207 I will try to get help on the CentOS list. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] PG84 and SELinux
On Sat, December 4, 2010 01:11, Tom Lane wrote: James B. Byrne byrn...@harte-lyne.ca writes: I wrote too soon. What I did was uncomment the ssl option. I neglected to change the setting from off to on. When I try to start the server with ssl=on it fails with this error: Auto configuration failed 29006:error:0E065068:configuration file routines:STR_COPY:variable has no value:conf_def.c:629:line 207 AFAIK there is no place in the standard Postgres sources that could emit an error message even vaguely like that. I'm guessing that you are using some add-on code that tries to parse postgresql.conf, but I don't know what that would be. Whose init script are you using? regards, tom lane I see some familiar names. . . # cat postgresql #!/bin/sh # postgresqlThis is the init script for starting up the PostgreSQL # server # # chkconfig: - 64 36 # description: Starts and stops the PostgreSQL backend daemon that handles \ # all database requests. # processname: postmaster # pidfile: /var/run/postmaster.pid # Version 6.5.3-2 Lamar Owen # Added code to determine if PGDATA exists, whether it is current version # or not, and initdb if no PGDATA (initdb will not overwrite a database). # Version 7.0 Lamar Owen # Added logging code # Changed PGDATA. # Version 7.0.2 Trond Eivind Glomsrd t...@redhat.com # use functions, add conditional restart # Version 7.0.3 Lamar Owen la...@postgresql.org # Check for the existence of functions before blindly using them # in particular -- check for success () and failure () before using. # More Cross-distribution support -- PGVERSION variable, and docdir checks. # Version 7.1 Release Candidate Lamar Owen la...@postgresql.org # initdb parameters have changed. # Version 7.1.2 Trond Eivind Glomsrd t...@redhat.com # Specify shell for su # Handle stop better - kill unwanted output, make it wait until the database is ready # Handle locales slightly differently - always using C isn't a valid option # Kill output from database initialization # Mark messages for translation # Version 7.1.2-2.PGDG Lamar Owen lamar.o...@wgcr.org # sync up. # Karl's fixes for some quoting issues. # Version 7.2b2 Lamar Owen lamar.o...@wgcr.org # version change. # Version 7.2 final. Lamar Owen lamar.o...@wgcr.org # reload from Peter E. # Eliminate the pidof postmaster test in stop -- we're using pg_ctl so we don't need pidof. # Tested the $? return for the stop script -- it does in fact propagate. # TODO: multiple postmasters. # Version 7.3 Lamar Owen lamar.o...@ramifordistat.net # Multiple postmasters, courtesy Karl DeBisschop # Version 7.4 Lamar Owen. # Version 7.4.3 Tom Lane t...@sss.pgh.pa.us # Support condstop for uninstall # Minor other changes suggested by Fernando Nasser. # Version 7.4.5 Tom Lane t...@sss.pgh.pa.us # Rewrite to start postmaster directly, rather than via pg_ctl; this avoids # fooling the postmaster's stale-lockfile check by having too many # postgres-owned processes laying about. # Version 8.1 Devrim Gunduz dev...@postgresql.org # Increased sleep time from 1 sec to 2 sec. # Version 8.2 Devrim Gunduz dev...@commandprompt.com # Set initdb as a seperate option. # Version 8.3 Devrim Gunduz dev...@commandprompt.com # Version 8.4 Devrim Gunduz dev...@commandprompt.com # Remove sameuser from initdb, to match the new hba conf file. # Get rid of duplicate PGDATA assignment. # Ensure pgstartup.log gets the right ownership/permissions during initdb # PGVERSION is the full package version, e.g., 8.4.0 # Note: the specfile ordinarily updates this during install PGVERSION=8.4.4 # PGMAJORVERSION is major version, e.g., 8.4 (this should match PG_VERSION) PGMAJORVERSION=`echo $PGVERSION | sed 's/^\([0-9]*\.[0-9]*\).*$/\1/'` -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] PG84 and SELinux
On Thu, December 2, 2010 15:32, James B. Byrne wrote: On Thu, December 2, 2010 15:23, Devrim GÜNDÜZ wrote: On Wed, 2010-12-01 at 16:54 -0500, Tom Lane wrote: AFAIK, the Red Hat RPMs work out-of-the-box with SELinux; They should -- we are using the same routines for initdb'ing. I will do a touch /.autorelabel and restart the server before I try again. If there was just something odd about the SELinux contexts on that particular host then that should clear it up. I will report whichever way it goes thereafter. I restarted the server this morning, waited for the relabel to finish ( a very long time ), and then upgraded to pg-8.4 without any problems. SSL works fine as well. I can only infer that something went seriously wrong with the SELinux context labels on that host. Thank you for the help. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] PG84 and SELinux
On Wed, December 1, 2010 16:54, Tom Lane wrote: James B. Byrne byrn...@harte-lyne.ca writes: Earlier today I attempted to upgrade a production server from 8.1 to 8.4 using the pgdg-84-centos.repo. I say attempted because I could never get it to support ssl connections and as that is a requirement I had to roll back to 8.1. Can't comment on that without a lot more detail. On Fri, December 3, 2010 07:40, James B. Byrne wrote: I restarted the server this morning, waited for the relabel to finish ( a very long time ), and then upgraded to pg-8.4 without any problems. SSL works fine as well. . . I wrote too soon. What I did was uncomment the ssl option. I neglected to change the setting from off to on. When I try to start the server with ssl=on it fails with this error: Auto configuration failed 29006:error:0E065068:configuration file routines:STR_COPY:variable has no value:conf_def.c:629:line 207 I have checked gpsql/data and the server.key and server.crt files are both present: -rw--- 1 postgres postgres 5213 Dec 12 2007 server.crt -rw--- 1 postgres postgres 1675 Dec 12 2007 server.key The only change made in the postgresql.conf file that triggered this was changing 'off' to 'on' for ssl. Changing it back to 'off' makes the problem disappear. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] PG84 and SELinux
On Thu, December 2, 2010 15:23, Devrim GÜNDÜZ wrote: On Wed, 2010-12-01 at 16:54 -0500, Tom Lane wrote: AFAIK, the Red Hat RPMs work out-of-the-box with SELinux; They should -- we are using the same routines for initdb'ing. I will do a touch /.autorelabel and restart the server before I try again. If there was just something odd about the SELinux contexts on that particular host then that should clear it up. I will report whichever way it goes thereafter. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Dumping a table from one database and adding it to another
I have read the documentation respecting backups but I cannot seem to find any mention of the specific case that I wish performed. I have a pair of tables in a production database that I wish to dump and then restore to a new, different database. I can, and probably will, recreate the tables and column layouts in the new database. Is there a way to load the data dumped from a single table in one database into a new, possibly differently named, table in a different database, using PG utilities? -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] Dumping a table from one database and adding it to another
On Thu, December 2, 2010 15:57, Adrian Klaver wrote: a different database, using PG utilities? pg_dump -U postgres -a -t cell_per -f cell_per.sql production followed by: /usr/bin/psql -f cell_per.sql production works wonderfully. Thank you. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PG84 and SELinux
OS: CentOS-5.5
Earlier today I attempted to upgrade a production server from 8.1 to
8.4 using the pgdg-84-centos.repo. I say attempted because I could
never get it to support ssl connections and as that is a requirement
I had to roll back to 8.1.
Whatever was the cause of the ssl problem I also encountered a
surprising number of SELinux violations. The following details the
SELinux settings that I ultimately had to apply as a local module.
This took a considerable period of time as each had to be triggered
in turn in order that the error be identified.
#= postgresql_t ==
allow postgresql_t var_lib_t:dir rmdir;
allow postgresql_t var_lib_t:file { write getattr link read unlink
append };
Is this to be expected?
--
*** E-Mail is NOT a SECURE channel ***
James B. Byrnemailto:byrn...@harte-lyne.ca
Harte Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PG_ERROR 42501 permissions error
It never rains but it pours they say. I am trying to extract (ad hoc) some data from my production database on the 8.1 service that I tried to upgrade to 8.4. I am using the same username and password as is used by a process hosted on the same server as the posgresql instance. That process works fine. However, when I try and connect from another server using those credentials then I see this in the log file: 2010-12-01 16:33:09 EST hll_theheart_devl 216.185.71.25(42531) hll_theheart_db_admin : LOG: 0: connection authorized: user=hll_theheart_db_admin database=hll_theheart_devl 2010-12-01 16:33:09 EST hll_theheart_devl 216.185.71.25(42531) hll_theheart_db_admin : LOCATION: BackendRun, postmaster.c:2780 2010-12-01 16:33:09 EST hll_theheart_devl 216.185.71.25(42531) hll_theheart_db_admin : ERROR: 55P02: parameter standard_conforming_strings cannot be changed 2010-12-01 16:33:09 EST hll_theheart_devl 216.185.71.25(42531) hll_theheart_db_admin : LOCATION: set_config_option, guc.c:3597 2010-12-01 16:33:09 EST hll_theheart_devl 216.185.71.25(42531) hll_theheart_db_admin : ERROR: 42501: permission denied for relation currencies 2010-12-01 16:33:09 EST hll_theheart_devl 216.185.71.25(42531) hll_theheart_db_admin : LOCATION: aclcheck_error, aclchk.c:1395 2010-12-01 16:33:09 EST hll_theheart_devl 216.185.71.25(42531) hll_theheart_db_admin : LOG: 0: disconnection: session time: 0:00:00.07 user=hll_theheart_db_admin database=hll_theheart_devl host=216.185.71.25 port=42531 2010-12-01 16:33:09 EST hll_theheart_devl 216.185.71.25(42531) hll_theheart_db_admin : LOCATION: log_disconnections, postgres.c:3608 I am particularly nonplussed over the 'ERROR: 55P02: parameter standard_conforming_strings cannot be changed' message. Can anyone here clue me in as to what I am doing wrong? -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] PG84 and SELinux
On Wed, December 1, 2010 16:54, Tom Lane wrote:
Whatever was the cause of the ssl problem I also encountered a
surprising number of SELinux violations. The following details
the
SELinux settings that I ultimately had to apply as a local module.
This took a considerable period of time as each had to be
triggered
in turn in order that the error be identified.
#= postgresql_t ==
allow postgresql_t var_lib_t:dir rmdir;
allow postgresql_t var_lib_t:file { write getattr link read unlink
append };
Is this to be expected?
AFAIK, the Red Hat RPMs work out-of-the-box with SELinux; I'm a bit
surprised to hear that the PGDG ones don't, because last I heard
they use the same file layout. What the above sounds like to me is
that
the data directory tree wasn't correctly labeled as postgresql_db_t.
Maybe a restorecon would have helped?
regards, tom lane
I tried a restorecon as suggested by sealert at the first error. It
had no effect insofar as I could determine.
--
*** E-Mail is NOT a SECURE channel ***
James B. Byrnemailto:byrn...@harte-lyne.ca
Harte Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3
--
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] PG84 and SELinux
On Wed, December 1, 2010 16:54, Tom Lane wrote: James B. Byrne byrn...@harte-lyne.ca writes: Earlier today I attempted to upgrade a production server from 8.1 to 8.4 using the pgdg-84-centos.repo. I say attempted because I could never get it to support ssl connections and as that is a requirement I had to roll back to 8.1. Can't comment on that without a lot more detail. Well, the only thing that I had to do was uncomment #ssl = off and set it to ssl = on. Then on service restart I obtained an error that looked similar to this: . . . configuration file routines:STR_COPY:variable has no value:conf_def.c:629:line 207 I cannot remember the exact error unfortunately and as I rolled back the update I have no way to reproduce it at will. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PGError: ERROR: missing FROM-clause entry for table
I am getting this error: PGError: ERROR: missing FROM-clause entry for table ca_customs_entry LINE 1: ..._entries.is_cadex_transmitted = 'f') ORDER BY ca_customs... The code is generated by a Ruby-on-Rails-3.0.1 ActiveRecord model: SELECT ca_customs_shipments.* FROM ca_customs_shipments INNER JOIN ca_customs_entries ON ca_customs_entries.ca_customs_shipment_id = ca_customs_shipments.id WHERE (ca_customs_entries.is_cadex_transmitted = 'f') ORDER BY ca_customs_entry.is_across_transmitted, ca_customs_entry.is_across_rejected, ca_customs_entry.is_across_accepted, ca_customs_entry.is_cadex_released LIMIT 5 OFFSET 0 From what I have been able to piece together for myself I suspect that the cause may be the absence of an 'AS alias' immediately following the 'FROM ca_customs_shipments' segment. PG evidently treats this as required element whereas standard SQL does not. If someone could confirm that my suspicions are well founded then I will raise an issue to handle the matter with the software maintainers. On the other hand, if there is another cause of this then I would like to have that information as well. Sincerely, -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] PGError: ERROR: missing FROM-clause entry for table
On Thu, November 25, 2010 21:58, Robert Treat wrote: On Thu, Nov 25, 2010 at 9:21 PM, James B. Byrne byrn...@harte-lyne.cawrote: Looks to me like the problem is you are trying to ORDER BY columns in ca_customs_entry, but there is no such table for that (don't confuse it with ca_customs_entries). You need to either set a matching alias, or fix the table name qualifier in those order by columns. That was exactly the problem. Thank you very much. I am afraid that the mental gymnastics that Rails requires--table names are plural, corresponding model classes are singular--often trips me up. I would never have seen that error on my own. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PG handling of date expressions
I encountered a situation wrt date expressions that, although I eventually resolved, has left me with a few unanswered questions regarding how PG handles dates. My error was in not encapsulating a programmically inserted date string within quotation marks. This meant that I was sending off a where clause that looked somewhat like this: WHERE 'date_of_interest' = 2009-07-18 Now, as the date of interest was, in all but one case, prior to 1970 this appeared to work. However, in one case the date was in 1999 and this was the record that exposed the error. I extrapolated, perhaps incorrectly, from my *nix experience and inferred that the timestamp value 'date_of_interest' used a *nix epoch time value and that the expression 2009-07-18 was resolving to 1984 at the DBMS. If true then this would account for the behaviour observed. However, it occurred to me that using the *nix epoch would be a very odd thing for a DBMS. So, on reconsideration I thought that perhaps the DBMS was using 1984 as the year value for comparison, which would also explain the observed behaviour. My question is: What is actually going on at teh DBMS when one sends a conditional clause comparing a date to a numeric expression such as the one above? -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to trace client sql requests?
I have a situation with a Rails project where test data in mysteriously disappearing in the middle of a test run. I would like to see the exact SQL of all client requests issued against a single table during a fixed time span. How can I best accomplish this in PostgreSQL? #client_min_messages = notice #log_min_messages = notice #log_min_duration_statement = -1 ... #log_duration = off Which of these, if any, should I alter; and to what? Am I constrained to system wide logging or can this be enabled by database? Regards, -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] How to trace client sql requests?
On Fri, July 10, 2009 14:58, hubert depesz lubaczewski wrote:
You can enable by database:
alter database x set log_min_duration_statement = 0;
Many, many thanks. Now of course I need more help...
The situation is that data inserted into the DB is not being found
on a subsequent select and I am unaware of any deletes being done.
So, I am hoping to find where the data is going or why the select is
not working.
This is the critical insert:
...
2009-07-10 15:13:00 EDT hll_theheart_test 216.185.71.24(57637)
hll_theheart_db_admin : LOCATION: exec_simple_query,
postgres.c:1105
2009-07-10 15:13:00 EDT hll_theheart_test 216.185.71.24(57637)
hll_theheart_db_admin : LOG: 0: duration: 1.366 ms statement:
INSERT INTO currencies (is_invoicable, is_payable,
changed_by, created_by, premium_factor, discount_factor,
effective_from, currency_name, superseded_after, changed_at,
currency_code, created_at) VALUES('f', 'f', E'not available',
E'not available', 1.0, 1.0, '1858-01-01 04:56:02.00', E'Canadian
Dollar', NULL, '2009-07-10 19:13:00', E'CAD', '2009-07-10
19:13:00.151885')
2009-07-10 15:13:00 EDT hll_theheart_test 216.185.71.24(57637)
hll_theheart_db_admin : LOCATION: exec_simple_query,
postgres.c:1105
2009-07-10 15:13:00 EDT hll_theheart_test 216.185.71.24(57637)
hll_theheart_db_admin : LOG: 0: duration: 0.379 ms statement:
SELECT currval('currencies_id_seq')
2009-07-10 15:13:00 EDT hll_theheart_test 216.185.71.24(57637)
hll_theheart_db_admin : LOCATION: exec_simple_query,
postgres.c:1105
2009-07-10 15:13:00 EDT hll_theheart_test 216.185.71.24(57637)
hll_theheart_db_admin : LOG: 0: duration: 0.073 ms statement:
RELEASE SAVEPOINT active_record_1
...
This seems to have worked. Would the log show if it did not?
The I see a bunch of these:
2009-07-10 15:13:02 EDT hll_theheart_test 216.185.71.24(57638)
hll_theheart_db_admin : LOG: 0: duration: 0.082 ms statement:
SET client_min_messages TO 'notice'
2009-07-10 15:13:02 EDT hll_theheart_test 216.185.71.24(57638)
hll_theheart_db_admin : LOCATION: exec_simple_query,
postgres.c:1105
2009-07-10 15:13:02 EDT hll_theheart_test 216.185.71.24(57638)
hll_theheart_db_admin : LOG: 0: duration: 6.155 ms statement:
SELECT a.attname, format_type(a.atttypid, a.atttypmod),
d.adsrc, a.attnotnull
2009-07-10 15:13:02 EDT hll_theheart_test 216.185.71.24(57638)
hll_theheart_db_admin : LOCATION: exec_simple_query,
postgres.c:1105
2009-07-10 15:13:02 EDT hll_theheart_test 216.185.71.24(57638)
hll_theheart_db_admin : LOG: 0: duration: 1.285 ms statement:
SELECT a.attname, format_type(a.atttypid, a.atttypmod),
d.adsrc, a.attnotnull
and finally, I get a long list of these:
2009-07-10 15:13:02 EDT hll_theheart_test 216.185.71.24(57638)
hll_theheart_db_admin : LOG: 0: duration: 1.779 ms statement:
SELECT * FROM currencies WHERE (currencies.currency_code =
E'CAD') LIMIT 1
...
I believe that this is what I want to examine. Is there a server
side technique that I can use which will tell me what data this
statement returned or if it found nothing?
In any case, I see the INSERTS and I can find NO DELETES at all. Is
there any other way to remove some or all data from a table?
--
*** E-Mail is NOT a SECURE channel ***
James B. Byrnemailto:byrn...@harte-lyne.ca
Harte Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[Fwd: Re: [GENERAL] How to trace client sql requests?]
On Fri, July 10, 2009 16:10, hubert depesz lubaczewski wrote: truncate. but first simple question - did you commit the inserts? But if it were done with truncate then I would see truncate in the log file, yes? Second, I am working with PG through an ORM called ActiveRecord, part of the Rails framework. I do not see a COMMIT anywhere in the log. Should I? This is a test run using the Rails test, actually cucumber, environment. I know that they do some things differently with DB connections in this environment but I believe that this is limited to transactions and rollbacks. The thing is that this problem only arises when testing the script inside the test harness. In production it runs just fine. Further, if I list the contents of the table from a call in the test harness immediately prior to executing the script under test then the data is there. I do not know what is going on. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] BR/
I am sorry for this but I do not know how else to communicate what
is apparently happening:
This is a portion of the log for the most recent run that exhibits
the problem:
...
2009-07-10 15:59:17 EDT hll_theheart_test 216.185.71.24(49133)
hll_theheart_db_admin : LOG: 0: duration: 0.446 ms statement:
INSERT INTO currencies (is_invoicable, is_payable,
changed_by, created_by, premium_factor, discount_factor,
effective_from, currency_name, superseded_after, changed_at,
currency_code, created_at) VALUES('f', 'f', E'not available',
E'not available', 0.9, 1.1, '1785-07-06 04:56:02.00', E'United
States Dollar', NULL, '2009-07-10 19:59:17', E'USD', '2009-07-10
19:59:17.634473')
That seems a valid insert.
2009-07-10 15:59:17 EDT hll_theheart_test 216.185.71.24(49133)
hll_theheart_db_admin : LOCATION: exec_simple_query,
postgres.c:1105
2009-07-10 15:59:17 EDT hll_theheart_test 216.185.71.24(49133)
hll_theheart_db_admin : LOG: 0: duration: 0.172 ms statement:
SELECT currval('currencies_id_seq')
2009-07-10 15:59:17 EDT hll_theheart_test 216.185.71.24(49133)
hll_theheart_db_admin : LOCATION: exec_simple_query,
postgres.c:1105
2009-07-10 15:59:17 EDT hll_theheart_test 216.185.71.24(49133)
hll_theheart_db_admin : LOG: 0: duration: 0.067 ms statement:
RELEASE SAVEPOINT active_record_1
This seems ok but the absence of proof does not ensure the absence
of error. If the insert failed would I see this fact reflected in a
log entry?
2009-07-10 15:59:17 EDT hll_theheart_test 216.185.71.24(49133)
hll_theheart_db_admin : LOCATION: exec_simple_query,
postgres.c:1105
2009-07-10 15:59:17 EDT hll_theheart_test 216.185.71.24(49133)
hll_theheart_db_admin : LOG: 0: duration: 0.782 ms statement:
SELECT * FROM currencies
The client program that receives this result reports that there are
no rows returned. So where did they go?
--
*** E-Mail is NOT a SECURE channel ***
James B. Byrnemailto:byrn...@harte-lyne.ca
Harte Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3
--
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] Inserted data is disappearing
On Fri, July 10, 2009 16:20, Bill Moran wrote: Also, look for a BEGIN statement that is never COMMITed. If the client starts a transaction, INSERTs a bunch of stuff, then disconnects without issuing a COMMIT, Postgres will rollback the transaction, thus it will be as if the data was never inserted. There is one ROLLBACK statement, but it occurs after all of the problems have evidenced themselves and not before. I believe this to be the Rails test harness unrolling the transaction that it wraps all test runs in. There is one BEGIN. This is located close to the very top of the run log, which seems congruent with the one ROLLBACK just before the very end. Evidently, all this test processing takes place within a single, never completed, transaction. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] BR/
On Fri, July 10, 2009 18:48, Scott Marlowe wrote: On Fri, Jul 10, 2009 at 2:13 PM, James B. Byrnebyrn...@harte-lyne.ca wrote: 2009-07-10 15:59:17 EDT hll_theheart_test 216.185.71.24(49133) hll_theheart_db_admin : LOCATION: exec_simple_query, postgres.c:1105 2009-07-10 15:59:17 EDT hll_theheart_test 216.185.71.24(49133) hll_theheart_db_admin : LOG: 0: duration: 0.782 ms statement: SELECT * FROM currencies The client program that receives this result reports that there are no rows returned. So where did they go? Maybe there were no rows to return?? Clearly there are no rows. That is the problem. The question begging an answer is: where are the rows added in the immediately previous INSERTS? -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] BR/
On Fri, July 10, 2009 18:13, Alvaro Herrera wrote: Is it using a different PG connection than the one doing the insert? In that case, it won't see the new row until the inserting transaction commits. That is almost certainly the exact problem. I will check and determine if this is so but I it seems to me unavoidable that launching a new shell for the script under test will cause another, different, connection to be used. If this proves the case then I will report back. If not then no doubt you will hear from me as well. Thank you for illuminating this for me. BTW it seems necessary to clarify that LOCATION lines correspond to the LOG/NOTICE/WARNING/ERROR line immediately _above_ it, not the one below. So noted, with thanks. Regards, -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PG_DUMP/RESTORE Would like an explanation of these (non-critical) errors
I move a compressed pg_dump archives across the wire to a remote host on a regular schedule. The process completes and the archives are restored on the remote site and the resulting database performs as expected. However, I get this returned to me at the end of each dump/transfer/restore pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 4; 2615 2200 SCHEMA public postgres pg_restore: [archiver (db)] could not execute query: ERROR: cannot drop schema public because other objects depend on it HINT: Use DROP ... CASCADE to drop the dependent objects too. Command was: DROP SCHEMA public; pg_restore: [archiver (db)] could not execute query: ERROR: schema public already exists Command was: CREATE SCHEMA public; WARNING: errors ignored on restore: 2 The pg_dump command is: pg_dump --create --format=c --user=postgres --verbose hll_redmine | gzip /var/data/pas-redmine/db/dump/hll_redmine.pgsql.gz rsync -avz --bwlimit=35 --delete-after --exclude=database.yml --exclude=*.log --exclude=*cache --exclude=*ruby_sess* /var/data/pas-redmine inet03.mississauga.harte-lyne.ca:/var/data 1 /dev/null The pg_restore command, which generates the error, is: gunzip /var/data/pas-redmine/db/dump/hll_redmine.pgsql.gz | pg_restore --clean --user=postgres --dbname=hll_redmine ; vacuumdb --user=postgres --full --analyze hll_redmine 1 /dev/null I speculate that I have set some options on the restore that conflict with those set on the dump, perhaps --create. Regrettably, I lack the expertise to determine if this is the cause or not; and, as this is a production environment, I lack the ability to play with them to determine what is incorrect. If anything is obviously wrong, or even mildly suspicious, I would appreciate a nudge in the right direction. This issue at least has no overlong dates. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] BETWEEN not matching on timestamp value
I have encountered an error that, on the face of it, seems to me to be inexplicable. I hope that someone here can illuminate the matter for me. PGError: ERROR: timestamp out of range: 20080809-01-01 00:00:00 : SELECT * FROM currency_exchange_rates WHERE (currency_code_base = E'CAD' AND currency_code_quote = E'JPY' AND effective_from BETWEEN '20080809-01-01 00:00:00' AND '20080809-01-01 23:59:59') ORDER BY currency_code_base, currency_code_quote, effective_from DESC Now, if I read this aright then, this is telling me that the timestamp value I am processing is 20080809-01-01 00:00:00 It is also telling me that this value does not lie between: '20080809-01-01 00:00:00' AND '20080809-01-01 23:59:59' If this is true then the BETWEEN operator must be EXCLUSIVE of its endpoints. However the manual says this about BETWEEN In addition to the comparison operators, the special BETWEEN construct is available. a BETWEEN x AND y is equivalent to a = x AND a = y Which says to me that the BETWEEN operator should be an INCLUSIVE match. SO, what is happening here? Have I missed something dead obvious? -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] BETWEEN not matching on timestamp value
On Tue, June 30, 2009 13:24, Tom Lane wrote: No, it's complaining that the constant is out of range --- it's failing long before it's tried to do any actual BETWEEN comparisons. Surely you meant something more like 2008-08-09? regards, tom lane The column is a timestamp value. I expected any time on any given date to fall between the start and end of that day so the hh:mm:ss:hh portion does not leap out at me as something that should cause a problem. Should it? The application system times are all UTC. ALTER TABLE currency_exchange_rates ADD COLUMN effective_from timestamp without time zone; ALTER TABLE currency_exchange_rates ALTER COLUMN effective_from SET STORAGE PLAIN; ALTER TABLE currency_exchange_rates ALTER COLUMN effective_from SET NOT NULL; -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] BETWEEN not matching on timestamp value]
On Tue, June 30, 2009 14:07, Tom Lane wrote: It's the eight-digit year field that it's unhappy with ... Duuuh! I suppose that it would... Thanks, -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] cygwin and postgresql
On: 15 Jun 2009 12:08:22 GMT, Jasen Betts ja...@xnet.co.nz On Sun, June 14, 2009 15:45, Scott Marlowe wrote: Is there a reason you're not using the native windows postgresql packages? Because for some reason, processes running in the cygwin environment could not create databases in the postgresql instance running in windows. that's odd, were you specifying host=localhost, if not it may be that the cygwin processes were trying to use local sockets. (oner difference of the native windows postgres to linux is no local sockets) The cygwin processes had no trouble connecting to and operating on existing databases. They could even drop a database. They just could not recreate one after dropping it. As the database in question is the one used for integration testing and as it gets dropped and recreated on the fly at various points in the test suite this was a major annoyance. It could very well be that this behaviour is caused by some other issue but I have a nagging suspicion that NTFS security issues are at the root of it. As I have no other need to access pgsql on my laptop, I thought it best to bring everything under one roof, so to speak, and begin checking things out from that initial point. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] cygwin and postgresql
On Mon, June 15, 2009 13:02, Scott Marlowe wrote: So, for sure something like: alter user dbuser with createdb; didn't fix the problem? I have removed the windows installation and can no longer check this. I maintained the pg roles via pgadmin3 and, to the best of my ability to recollect, the owner of the development and test databases had dbcreate privileges. If the problem persists under the cygwin environment then I will revisit the native pg installation for testing. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] cygwin and postgresql
This may not be the right place for this question but I am going to start here anyway. I have installed postgresql in cygwin on a MS-WinXPpro system. I wish to initialise a database instance. However, I cannot find any program file called initdb. In /usr/bin I can see files like: pg_config.exe, pg_dump.exe and pg_dumpall.exe but no initdb.exe anywhere. Is there something about initdb that I do not understand or some reason why it would not form part of the cygwin installation? -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] cygwin and postgresql
On Sun, June 14, 2009 13:34, Tom Lane wrote: That sounds suspiciously like a client-only installation. What package did you install exactly? Was there a -server package beside it? The packages that cygwin says I have installed are: 8.2.11-1 postgresql: PostgreSQL Data Base Management System 3,707k 8.2.11-1 postgresql-client: Front-end programs for PostgreSQL 8.x 972k 8.2.11-1 postgresql-contrib ... 373k 8.2.11-1 postgresql-devel: ... server side programs 488k 8.2.11-1 postgresql-doc 8.2.11-1 postgresql-plperl and that is all. There is not a package listed with -server in it. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] cygwin and postgresql
On Sun, June 14, 2009 15:45, Scott Marlowe wrote: why it would not form part of the cygwin installation? Is there a reason you're not using the native windows postgresql packages? Because for some reason, processes running in the cygwin environment could not create databases in the postgresql instance running in windows. As I develop for Linux and use cygwin to (mostly) replicate that environment on my MS-Win laptop I thought that moving everything having to do with the development environment under cygwin would be best. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] cygwin and postgresql
Found it. The cygwin executables for postgres are installed under /usr/sbin, which is NOT in the PATH thus the problem, and the obvious solution. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] search for partial dates
On Thu, June 11, 2009 17:37, Andy Colson wrote: That's a little vague, so how about: select * from somethine where (extract(year from idate) = $1) or (extract(year from idate) = $2 and extract(month from idate) = $3) or (extract(year from idate) = $4 and extract(month from idate) = $5 and extract(day from idate) = $6) Actually, I am thinking that perhaps this is better accomplished by parsing the data in the application and generating a date range that I then pass as parameters to a PG BETWEEN condition: For example: given 2008 then SD = 2008010101 and ED = 20081231235959 given 200805 then SD = 2008050101 and ED = 20080531235959 given 20080709 then SD = 2008070901 and ED = 20080709235959 I believe that this construction should work and also make use of the index SELECT * WHERE effective_from BETWEEN SD and ED Is my appreciate correct? -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] search for partial dates
Given a datetime column, not null, is there a single syntax that permits searching for all dates in a given year, year+month, and year+month+day such that a single parameterised query can handle all three circumstances? -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] Help with join syntax sought supplemental
On Thu, May 21, 2009 06:02, Alban Hertroys wrote: But as people often say here, premature optimisation is a waste of time, so don't go that route unless you have a reason to expect problems in that area. That was my very thought when I sent that message. On the other hand, in case I was doing something out of ignorance that was notoriously wrong, it seemed best to ask. Regards, -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] Help with join syntax sought
This seems to be working. I had to take a different approach as I had misapprehended GROUP BY completely. SELECT * FROM currency_exchange_rates AS xchg1 WHERE id IN ( SELECT id FROM currency_exchange_rates as xchg2 WHERE xchg1.currency_code_base = xchg2.currency_code_base AND xchg1.currency_code_quote = xchg2.currency_code_quote ORDER BY currency_code_base, currency_code_quote, effective_from DESC LIMIT 1 ) ORDER BY currency_code_base, currency_code_quote, effective_from DESC -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] Help with join syntax sought supplemental
On Wed, May 20, 2009 13:07, James B. Byrne wrote: This seems to be working. I had to take a different approach as I had misapprehended GROUP BY completely. SELECT * FROM currency_exchange_rates AS xchg1 WHERE id IN ( SELECT id FROM currency_exchange_rates as xchg2 WHERE xchg1.currency_code_base = xchg2.currency_code_base AND xchg1.currency_code_quote = xchg2.currency_code_quote ORDER BY currency_code_base, currency_code_quote, effective_from DESC LIMIT 1 ) ORDER BY currency_code_base, currency_code_quote, effective_from DESC Looking at this I have to wonder what will be the effect of having tens of thousands of rate-pairs on file. Would this query be improved by first doing a sub-query on base/quote pairs that returned DISTINCT pairs and then do the IN condition using that? -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] Help with join syntax sought
On Tue, May 19, 2009 17:43, Andy Colson wrote: . What field is the source? currency_code_quote? -Andy Here is the layout of the table: # Table name: currency_exchange_rates # # id :integer not null, primary key # currency_code_base :string(3) not null # currency_code_quote :string(3) not null # currency_exchange_rate :decimal(12, 6) not null # currency_exchange_source :string(255) not null # currency_exchange_type :string(4) not null # effective_from :datetimenot null # superseded_after :datetime # created_at :datetimenot null # created_by :string(255) default(unknown), #not null # changed_at :datetimenot null # changed_by :string(255) default(unknown), #not null # I appreciate the help and I do not wish to burden you with too much detail. The situation is that currency pairs are assigned a nominal exchange rate by various markets based on actual trades. We read these rates from various sources and assign a type to that rate pair. We record the rate, the exact source of the rate and the datetime of its effectiveness. Rates, by their market driven nature, are always historic, so that it is not foreseen that an effective_from time-stamp will ever be in the future. What I want to be able to do is to return the most recent rate for all unique rate-pairs, irrespective of type. I also have the requirement to return the 5 most recent rates for each rate-pair, thus the HAVING count(*) = 1, which I thought would allow me to simply change the test to HAVING count(*) = 5 in that case. I am still feeling my way though SQL syntax and some of the results I am seeing simply do not make sense to me, at the moment. Some of my questions therefore may appear rather naive. The GROUP BY clause in particular is giving me trouble at the moment. I rather suspect that I have missed an important distinction with respect to GROUP BY and ORDER BY. GROUP BY I am now beginning to see is an accumulator, whereas I have been considering it more or less as a substitute for a report breakpoint. Given this: CAD AUD 2009-05-19 16:15:00 NOON 1.146300 CAD AUD 2009-05-19 20:40:00 CLSE 1.131200 CAD AUD 2009-05-17 16:15:00 NOON 1.151300 CAD AUD 2009-05-17 20:40:00 CLSE 1.141100 CAD AUD 2009-05-16 16:15:00 NOON 1.143700 CAD AUD 2009-05-16 20:40:00 CLSE 1.142100 CAD USD 2009-05-19 16:15:00 NOON 0.864400 CAD USD 2009-05-19 20:40:00 CLSE 0.843100 What I want to see in the final output is CAD AUD 2009-05-19 20:40:00 CLSE 1.131200 CAD USD 2009-05-19 20:40:00 CLSE 0.843100 and if requested for the 5 most recent then I want to see this: CAD AUD 2009-05-19 20:40:00 CLSE 1.131200 CAD AUD 2009-05-19 16:15:00 NOON 1.146300 CAD AUD 2009-05-17 20:40:00 CLSE 1.141100 CAD AUD 2009-05-17 16:15:00 NOON 1.151300 CAD AUD 2009-05-16 20:40:00 CLSE 1.142100 CAD USD 2009-05-19 20:40:00 CLSE 0.843100 CAD USD 2009-05-19 16:15:00 NOON 0.864400 -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
