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 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
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
[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
[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
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
[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] 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
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
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
[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
[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
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] 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] 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 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
[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 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 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 Sat, December 4, 2010 01:11, Tom Lane wrote: > "James B. Byrne" 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 # use functions, add conditional restart # Version 7.0.3 Lamar Owen # 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 # initdb parameters have changed. # Version 7.1.2 Trond Eivind Glomsrd # 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 # sync up. # Karl's fixes for some quoting issues. # Version 7.2b2 Lamar Owen # version change. # Version 7.2 final. Lamar Owen # 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 # Multiple postmasters, courtesy Karl DeBisschop # Version 7.4 Lamar Owen. # Version 7.4.3 Tom Lane # Support condstop for uninstall # Minor other changes suggested by Fernando Nasser. # Version 7.4.5 Tom Lane # 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 # Increased sleep time from 1 sec to 2 sec. # Version 8.2 Devrim Gunduz # Set initdb as a seperate option. # Version 8.3 Devrim Gunduz # Version 8.4 Devrim Gunduz # 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 Wed, December 1, 2010 16:54, Tom Lane wrote: > "James B. Byrne" 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: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] 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] 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] 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
Re: [GENERAL] PG84 and SELinux
On Wed, December 1, 2010 16:54, Tom Lane wrote: > "James B. Byrne" 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
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
[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
[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
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 > wrote: >> > > 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] 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
[GENERAL] Removing duplicates from multiple self left joins
I am dynamically generating a query like below that creates different combinations of rules by left joining (any number of times) on itself and avoiding rules with some of the same attributes as part of the joins conditions e.g. SELECT count(*) FROM rules AS t1 LEFT JOIN rules AS t2 ON t1.id != t2.id AND ... LEFT JOIN rules AS t3 ON t1.id != t2.id AND t1.id != t3.id AND t2.id != t3.id AND ... I am currently removing duplicates by creating an array of ids from the joined rows then sorting and grouping by them: SELECT sort(array[t1.id, t2.id, t3.id]) ... GROUP BY ids I would like to know if there is a better way of removing duplicate rows e.g. t1.ID | t2.ID | t3.ID - A | B | C C | B | A Should be t1.ID | t2.ID | t3.ID - A | B | C Or t1.ID | t2.ID | t3.ID - C | B | A But not both. I would like to go from a permutation of rows to a combination rows.
[GENERAL] Compiling openssl
Good day, Is it possible to use any of your products to compile openssl-1.0.0a.tar.gz form http://www.openssl.org/source/ for Win32 environment, how? OR can you help complile it with this adjustment to the makefile: To build, I needed to modify the first lines of the example code's Makefile as follows: OPENSSLDIR=c:/openssl32 CFLAGS=-g -I$(OPENSSLDIR)/include LD=-Lc:/openssl32/lib -llibeay32 -lssleay32 Please help or advice on the way out. Regards, Yomi
[GENERAL] Postgres standard versus Postgres Plus Advanced Server
Hi. I'm setting up a new workstation (Win7 64bit Quad Core 4GB) with Postgres, for development work, and trying to pick which version I should install. Most of the time, Postgres is dormant - I'm not using it all - but when I do use it, the load can be high, and I want maximum performance. Is there any reason to use standard Postgres over Postgres Plus Advanced Server? My understanding is "Plus" adds some various tools like standard database connectors, and that Advanced Server adds Oracle compatibility (which I won't use) and performance improvements (which are always welcome). Why not get those performance improvements? Will they cause Postgres to use more resources when dormant? Why not just go with Plus Advanced? Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Verifying a PITR
What would be the best way to verify that a PITR came up with *all* the expected data? This is mostly for a controlled failover, where I manually bring down the primary server, and shouldn't ever lose a transaction. If I need to use something like txid_current(), how do I ensure that it's the last transaction before shutdown, and the first after recovery? Thanks -jim -- 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] warm standby and reciprocating failover
On Mon, Aug 24, 2009 at 12:45 PM, james bardin wrote: >> >> I tried recovery_target_timeline='X' on the standby, where X is the >> new timeline created after recovery on the new master. This fails, >> with some "unexpected timeline ID" lines and a >> PANIC: could not locate a valid checkpoint record >> >> I also tried using recovery_target_timeline='latest'. This fell back >> gracefully to an earlier state, but changes were lost. Also, it never >> waited on pg_standby, and finished recovering immediately. It seems that this is related the the issue in this bug report: http://archives.postgresql.org/pgsql-bugs/2009-05/msg00060.php The follow up is very long, and I couldn't formulate any workaround for the issue. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] warm standby and reciprocating failover
I wasn't sure which list is better suited, so this is cross posted from pgsql-admin. -Thanks On Fri, Aug 21, 2009 at 10:46 AM, james bardin wrote: > I have a working warm standby system, running 8.4 (thanks for urging > me to upgrade from the rehdat provided release). > One of the new requirements is going to be for (a non-DBA) admin to > easily swap services between the two servers for maintenance. > > The first move runs easily as expected- postgres ships the last > partial wal immediately on shutdown, trigger the standby and we're up. > I'm now running into issues bringing the first server back up in > standby mode. After the second server finishes recovery, the major > number of the wal files is incremented (say from 0001 to > 0002), and the 0002.history file is shipped back to the first > server. The first server however is still looking for 0001x files. > > Is there a way to ship back the missing information from the recovery > process, without doing another base backup of data/ ? On Mon, Aug 24, 2009 at 11:34 AM, james bardin wrote: > So I've been experimenting with this timeline problem without any success. > Is it possible that there are changes made during recovery that aren't logged? > > > I tried recovery_target_timeline='X' on the standby, where X is the > new timeline created after recovery on the new master. This fails, > with some "unexpected timeline ID" lines and a > PANIC: could not locate a valid checkpoint record > > I also tried using recovery_target_timeline='latest'. This fell back > gracefully to an earlier state, but changes were lost. Also, it never > waited on pg_standby, and finished recovering immediately. > > Although it doesn't solve this problem, can pg_standby be used with > recovery_target_timeline='latest', or should I file a bug? > > Thanks > -jim -- 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] Relational Algebra and Aggregate Functions
On Tue, Jul 28, 2009 at 9:47 AM, Sam Mason wrote:
> On Tue, Jul 28, 2009 at 09:14:38AM -0400, Robert James wrote:
> > Many wrote that the functional programming 'fold' is a good model for
> > relational aggregate functions. I have a few difficulties with this:
> > 1. fold doesn't offer any type of GROUP BY, which is an essential
> component
> > of aggregation.
>
> Not sure if I'd agree, a GROUP BY without any aggregate functions looks
> pretty indistinguishable from just a DISTINCT on the same columns to me.
>
> DISTINCT will collapse duplicates, which is not what we want when computing
COUNT, SUM, or AVG - please see below.
> > 3. fold is defined on sequences, not sets. This doesn't seem to be a
> > problem until you think about cases where there a duplicates of the
> > aggregated field. (For instance, there are 10 bags each weighing 5 lbs,
> and
> > you want SUM(weight) - you need to project weight onto a collection which
> > allows for 10 occurences, or define the aggregate function to work on the
> > whole tuple somehow... I know a man named Krug worked out a formal theory
> > for this...)
>
> I don't see why this is a problem at all; could you give a concrete
> example?
>
Relation LUGGAGE = { (name:'ball', weight:3), (name:'bat', weight:3)}
How do we formalize SELECT SUM(weight) FROM LUGGAGE? We could
project_weight(LUGGAGE) and then apply SUM, except that would give us
{(weight:3), (weight:3)}, which is not a set (it has duplicates). We could
define a new operation: project_to_list (allowing duplicates), or we could
define SUM(weight) over the LUGGAGE relation as a whole - either way, we
need to extend the theory a bit.
Re: [GENERAL] Relational Algebra and Aggregate Functions
Thanks! "SQL and Relational Theory: How to Write Accurate SQL Code" looks like the best pick of the bunch. On Tue, Jul 28, 2009 at 10:08 AM, Michael Glaesemann wrote: > > On Jul 27, 2009, at 21:05 , Robert James wrote: > > 2) Database in Depth: Relational Theory for Practitioners >> >> http://www.amazon.com/Database-Depth-Relational-Theory-Practitioners/dp/0596100124/ref=sr_1_7?ie=UTF8&s=books&qid=1248742811&sr=1-7 >> > > "Database in Depth" is good, though he's effectively rewritten it as "SQL > and Relational Theory: How to Write Accurate SQL Code" > > http://www.amazon.com/SQL-Relational-Theory-Write-Accurate/dp/0596523068 > > Michael Glaesemann > grzm seespotcode net > > > >
Re: [GENERAL] Clients disconnect but query still runs
I see - thanks, Tom, for the informative explanation. In my experience admining high volume servers, I found this to a major failure pattern: Client tries query which seems to go on forever (either do to contention or resource exhaustion or some other problem), client gives up / fails / gets shut down or rebooted, yet the database is left hanging working on the slw query, which is probably consuming all of its resources. Perhaps the client restarts and tries again, now making the problem much worse, and the vicious cycle continues until the server is rebooted. Is there no way to have the OS interrupt the postgres process when a TCP/IP disconnect happens? Or is the OS also in the dark that the TCP/IP connection was dropped? I believe that there is a way to monitor this using TCP/IP keep alives. Or perhaps Postgres could check once every minute? Either way, in my experience, solving this would be a major boon to high volume servers, at least in the usage patterns I've worked with. On Mon, Jul 27, 2009 at 9:49 PM, Tom Lane wrote: > Robert James writes: > > Hi. I noticed that when clients (both psql and pgAdmin) disconnect or > > cancel, queries are often still running on the server. A few questions: > > 1) Is there a way to reconnect and get the results? > > No. > > > 2) Is there a way to tell postgres to automatically stop all queries when > > the client who queried them disconnects? > > No. > > > 3) Is there a way to see all queries whose clients have disconnected? > > No. > > > 4) And finally: Why is this the behavior? > > It's not easy to tell whether a client has disconnected (particularly if > the network stack is unhelpful, which is depressingly often true). > Postgres will cancel a query if it gets told that the connection's been > dropped, but it will only discover this when an attempt to output to the > client fails. It does not spend cycles looking aside to see if the > connection has dropped when it is doing something that doesn't involve > output to the client. > > If your client code is polite enough to send a cancel request before > disconnecting, that should terminate the query reasonably promptly. > But just "yanking the plug" doesn't do that. > >regards, tom lane >
Re: [GENERAL] Relational Algebra and Aggregate Functions
Many wrote that the functional programming 'fold' is a good model for relational aggregate functions. I have a few difficulties with this: 1. fold doesn't offer any type of GROUP BY, which is an essential component of aggregation. 2. I don't believe fold can handle things like AVG() or STDDEV(). Can it? Conversely, fold can handle non-commutative and non-associative operators, which I don't believe can be used for aggregation. 3. fold is defined on sequences, not sets. This doesn't seem to be a problem until you think about cases where there a duplicates of the aggregated field. (For instance, there are 10 bags each weighing 5 lbs, and you want SUM(weight) - you need to project weight onto a collection which allows for 10 occurences, or define the aggregate function to work on the whole tuple somehow... I know a man named Krug worked out a formal theory for this...)
Re: [GENERAL] Relational Algebra and Aggregate Functions
Thanks for all the good replies (both on and off list). It seems the consensus is for me to read Christopher Date. I found two relevant Date books: 1) Introduction to Database Systems http://www.amazon.com/Introduction-Database-Systems-Kannan-Swamynathan/dp/B001BVYKY4/ref=sr_1_5?ie=UTF8&s=books&qid=1248742811&sr=1-5 and 2) Database in Depth: Relational Theory for Practitioners http://www.amazon.com/Database-Depth-Relational-Theory-Practitioners/dp/0596100124/ref=sr_1_7?ie=UTF8&s=books&qid=1248742811&sr=1-7 Any recommendations as to which? From the titles, I'd be inclined towards the second, but not if the first is better. One thing I'm not interested in is polemics against SQL and lamentations on how ignorant all practitioners are. On Mon, Jul 27, 2009 at 2:45 PM, Jeff Davis wrote: > On Sun, 2009-07-26 at 15:36 -0400, Robert James wrote: > > I'm working on improving my background database theory, to aid in > > practice. I've found learning relational algebra to be very helpful. > > One thing which relational algebra doesn't cover is aggregate > > functions. Can anyone recommend any papers or web pages which provide > > some good theoretical background for aggregate functions? > > When it comes to relational theory, C.J. Date is a good author. "An > Introduction To Database Systems" covers pretty much everything. > > There's a formal definition of a relational algebra (including > SUMMARIZE, which is the authors' version of an aggregate operator) > defined with only two operators here: > http://thethirdmanifesto.com/ > (look for "Appendix A") > > Although Appendix A is not easy to understand without some basic > familiarity with the authors' other works. > > Regards, > Jeff Davis > >
[GENERAL] Clients disconnect but query still runs
Hi. I noticed that when clients (both psql and pgAdmin) disconnect or cancel, queries are often still running on the server. A few questions: 1) Is there a way to reconnect and get the results? 2) Is there a way to tell postgres to automatically stop all queries when the client who queried them disconnects? 3) Is there a way to see all queries whose clients have disconnected? 4) And finally: Why is this the behavior? Doesn't this keep some very long queries running which drain performance but don't seem to benefit anyone?
[GENERAL] Ruuning two instances of Postgres on the same machine
I'm currently running Postgres 8.2 on Windows XP. I would like to use some 8.4 features, but I don't want to migrate my 8.2. Is there any way to run both instances together? Are there any problems with that? Alternatively, is the procedure to move from 8.2 to 8.4 without data or function loss documented anywhere? Thanks
[GENERAL] Relational Algebra and Aggregate Functions
I'm working on improving my background database theory, to aid in practice. I've found learning relational algebra to be very helpful. One thing which relational algebra doesn't cover is aggregate functions. Can anyone recommend any papers or web pages which provide some good theoretical background for aggregate functions?
[GENERAL] Transitive Closure and CONNECT BY
Is there a transitive closure (or equivalent) operator in Postgres (or extension)? Anything like CONNECT BY? Or any recommended way of querying hiearchial data?
[GENERAL] Help using SELECT INTO to make schema
I'd like to SELECT INTO one table into another one. However, I'd like to do two things that I don't know how to do using SELECT INTO: 1. Copy over the indexes and constraints of the first table into the second 2. Do SELECT INTO even if the second table already exists. Is there anyway to do either one of those? Failing that, is there a way to copy a table's schema - its columns, indexes, and constraints - into a new (empty) table?
Re: [GENERAL] Can LIKE under utf8 use INDEXes?
Thanks - I don't show any locale: rbt_development=> \l List of databases Name | Owner | Encoding +-+-- rbt_development | rbt | UTF8 ... On Wed, Jul 22, 2009 at 6:45 PM, Andreas Wenk < a.w...@netzmeister-st-pauli.de> wrote: > Robert James wrote: > >> Thank you, Tom. I guess I'm a bit confused about things here. How can I >> find the locale of my database? (I wasn't able to find this in the docs). >> If I do have the locale set to 'C', do I loose anything by using utf8 for >> all text fields? >> > > use psql: > > postgres=# \l+ >List of databases > Name | Owner | Encoding | Collation |Ctype| > +---+--+-+-+ > postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | > > output shortend ;-) > > Cheers > > Andy > > > > >
Re: [GENERAL] Can LIKE under utf8 use INDEXes?
Thank you, Tom. I guess I'm a bit confused about things here. How can I find the locale of my database? (I wasn't able to find this in the docs). If I do have the locale set to 'C', do I loose anything by using utf8 for all text fields? On Wed, Jul 22, 2009 at 4:31 PM, Tom Lane wrote: > Robert James writes: > > Hi. I'm confused about the behavior of LIKE under utf8 locale. > > UTF8 is not a locale, it's an encoding. If you're using C locale then > LIKE can use indexes, regardless of the encoding. If you're using > some other locale then you need a pattern_ops index. > >regards, tom lane >
Re: [GENERAL] Documentation Improvement suggestions
On Mon, Jul 20, 2009 at 11:37 AM, Martijn van Oosterhout wrote: > I know it's not easy, but a nice option to me would be if the 8.1 docs > page linked to the equivalent page in the other versions. That would > avoid the need to manually edit the URL after a google search. > > Oh, and +10 for the "Up" link at the top of the page also. > +1 (!) This would solve all of the docs problems I mentioned - include a line at the top stating "This is the documentation for version 8.1. Hyperlink:See documentation of current version (8.4)". And, yes, an "Up" at the top would be super helpful - reduce about 50% of searches.
[GENERAL] Can LIKE under utf8 use INDEXes?
Hi. I'm confused about the behavior of LIKE under utf8 locale. Accoding to the docs ( http://www.postgresql.org/docs/8.2/interactive/locale.html - excerpted below), it seems that LIKE ignores locale and hence can't use indexes. Yet, EXPLAIN clearly shows it using indexes. The docs suggest a workaround, to allow LIKE to use indexes - but I couldn't figure it out. Although I'm stuck with locale utf8, all my data is 7-bit ascii. I'm doing a tremendous amount of WHERE x LIKE 'abc%' - what's the best way to set up a good index? (I can change the settings for this database - but the cluster must remain utf8). Thanks! (Here is the doc excerpt, from http://www.postgresql.org/docs/8.2/interactive/locale.html : "The drawback of using locales other than C or POSIX in PostgreSQL is its performance impact. It slows character handling and prevents ordinary indexes from being used by LIKE. For this reason use locales only if you actually need them. As a workaround to allow PostgreSQL to use indexes with LIKE clauses under a non-C locale, several custom operator classes exist. These allow the creation of an index that performs a strict character-by-character comparison, ignoring locale comparison rules. Refer to Section 11.8 for more information.")
[GENERAL] Understanding INNER JOIN versus IN subquery
I have two queries which should be equivalent. The Planner plans them
differently, although they are both about the same time. Can someone
explain why?
select word from dict
where
word in
(select substr('moon', 0, generate_series(3,length('moon'
select * from dict
inner join (select substr('moon', 0, generate_series(3,length('moon' as
m
on dict.word = m.substr
Is one preferred?
[GENERAL] Documentation Improvement suggestions
Two small suggestions that might make it easier for newcomers to take advantage of the wonderful database: 1. Googling Postgres docs produces links for all different versions. This is because incoming links are to different versions. Besides being confusing, it pushes the pages lower in Google, and makes it harder to find them. Could the website offer a link to the 'current' version, whichever it is. Eg instead of just : http://www.postgresql.org/docs/8.1/static/creating-cluster.html Have: http://www.postgresql.org/docs/current/static/creating-cluster.html which would keep all incoming links pointed to the current page. 2. The 'SQL' in 'PostgresSQL' is hard to say and type. Everyone drops it (even this list!). Why not change the official name? Again, it would make googling and naming things easier.
Re: [GENERAL] Understanding sequential versus index scans.
Yes, I had done UNION. UNION ALL achives the expected plan and speed! Thank you! BTW, this is interesting, because there are only about 5 or 6 rows max returned from both queries - but I guess the planner expects more and hence changes the plan to remove duplicates. On Sun, Jul 19, 2009 at 9:05 PM, Scott Marlowe wrote: > On Sun, Jul 19, 2009 at 6:10 PM, Robert James > wrote: > > UNION was better, but still 5 times as slow as either query done > > individually. > > set enable_seqscan=off didn't help at all - it was totally ignored > > Is there anything else I can do? > > Did you try union, or union all? >
Re: [GENERAL] Understanding sequential versus index scans.
Is there anyway to tell Postgres "Run these two queries, and union their
results, but don't change the plan as to a UNION - just run them
separately"?
Something seems funny to me that running a UNION should be twice as slow as
running the two queries one after the other.
On Sun, Jul 19, 2009 at 8:10 PM, Robert James wrote:
> UNION was better, but still 5 times as slow as either query done
> individually.
> set enable_seqscan=off didn't help at all - it was totally ignored
> Is there anything else I can do?
>
> On Sun, Jul 19, 2009 at 7:47 PM, Tom Lane wrote:
>
>> Robert James writes:
>> > Hi. I notice that when I do a WHERE x, Postgres uses an index, and when
>> I
>> > do WHERE y, it does so as well, but when I do WHERE x OR y, it
>> > doesn't.
>>
>> It can use indexes for OR conditions, but not for arbitrary OR
>> conditions...
>>
>> > select * from dict
>> > where
>> > word in (select substr('moon', 0, generate_series(3,length('moon'
>> --
>> > this is my X above
>> > OR word like 'moon%' -- this is my Y above
>>
>> ... and that one is pretty arbitrary. You might have some luck with
>> using a UNION instead, viz
>>
>> select * from dict where X
>> union all
>> select * from dict where Y
>>
>>regards, tom lane
>>
>
>
Re: [GENERAL] Should I CLUSTER on PRIMARY KEY
Thanks, Chris. Is there a way to do this deterministically, or at least programatically? I have code to create the tables and cluster them automatically? On Sun, Jul 19, 2009 at 8:21 PM, Chris wrote: > Robert James wrote: > >> I would like to CLUSTER a table on its PRIMARY KEY. Now, I haven't >> explicitly defined and named an index for this table - but the primary key >> defines one. How can I tell Postgres to CLUSTER on it? >> > > Get the index name: > > \d tablename > > Right at the bottom it will have the index names: > > Indexes: >"a_pkey" PRIMARY KEY, btree (a) > > > then cluster: > > # cluster tablename using a_pkey; > CLUSTER > > > Also: If I define an index on a PK, will Postgres make a second one, or >> realize its redundnant? >> > > Depends how you define it (I think). What's your create table statement > look like? > > -- > Postgresql & php tutorials > http://www.designmagick.com/ > >
Re: [GENERAL] Understanding sequential versus index scans.
UNION was better, but still 5 times as slow as either query done
individually.
set enable_seqscan=off didn't help at all - it was totally ignored
Is there anything else I can do?
On Sun, Jul 19, 2009 at 7:47 PM, Tom Lane wrote:
> Robert James writes:
> > Hi. I notice that when I do a WHERE x, Postgres uses an index, and when
> I
> > do WHERE y, it does so as well, but when I do WHERE x OR y, it
> > doesn't.
>
> It can use indexes for OR conditions, but not for arbitrary OR
> conditions...
>
> > select * from dict
> > where
> > word in (select substr('moon', 0, generate_series(3,length('moon' --
> > this is my X above
> > OR word like 'moon%' -- this is my Y above
>
> ... and that one is pretty arbitrary. You might have some luck with
> using a UNION instead, viz
>
> select * from dict where X
> union all
> select * from dict where Y
>
>regards, tom lane
>
[GENERAL] Should I CLUSTER on PRIMARY KEY
I would like to CLUSTER a table on its PRIMARY KEY. Now, I haven't explicitly defined and named an index for this table - but the primary key defines one. How can I tell Postgres to CLUSTER on it? Also: If I define an index on a PK, will Postgres make a second one, or realize its redundnant? Thanks!
Re: [GENERAL] Understanding sequential versus index scans.
PS Running "PostgreSQL 8.2.1 on i686-pc-mingw32, compiled by GCC gcc.exe
(GCC) 3.4.2 (mingw-special)"
On Sun, Jul 19, 2009 at 6:58 PM, Robert James wrote:
> Hi. I notice that when I do a WHERE x, Postgres uses an index, and when I
> do WHERE y, it does so as well, but when I do WHERE x OR y, it doesn't. Why
> is this so? And how can I shut this off?
> select * from dict
> where
> word in (select substr('moon', 0, generate_series(3,length('moon' --
> this is my X above
> OR word like 'moon%' -- this is my Y above
>
> Seq Scan on dict (cost=0.02..2775.66 rows=30422 width=24) (actual
> time=16.635..28.580 rows=8 loops=1)
> Filter: ((hashed subplan) OR ((word)::text ~~ 'moon%'::text))
> SubPlan
> -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.014..0.019
> rows=2 loops=1)
> Total runtime: 28.658 ms
> (Using just X or Y alone uses the index, and completes in 0.150 ms)
> Is this a bug?
>
>
>
>
>
[GENERAL] Understanding sequential versus index scans.
Hi. I notice that when I do a WHERE x, Postgres uses an index, and when I
do WHERE y, it does so as well, but when I do WHERE x OR y, it doesn't. Why
is this so? And how can I shut this off?
select * from dict
where
word in (select substr('moon', 0, generate_series(3,length('moon' --
this is my X above
OR word like 'moon%' -- this is my Y above
Seq Scan on dict (cost=0.02..2775.66 rows=30422 width=24) (actual
time=16.635..28.580 rows=8 loops=1)
Filter: ((hashed subplan) OR ((word)::text ~~ 'moon%'::text))
SubPlan
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.014..0.019 rows=2
loops=1)
Total runtime: 28.658 ms
(Using just X or Y alone uses the index, and completes in 0.150 ms)
Is this a bug?
[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
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
Re: [GENERAL] BR/
On Fri, July 10, 2009 18:48, Scott Marlowe wrote: > On Fri, Jul 10, 2009 at 2:13 PM, James B. > Byrne 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] 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
[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] 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
[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
[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
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] 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
[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] 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
Re: [GENERAL] cygwin and postgresql
On: 15 Jun 2009 12:08:22 GMT, Jasen Betts > > 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
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] 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
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
[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] 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 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
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
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
Re: [GENERAL] Help with join syntax sought
On Tue, May 19, 2009 17:02, Andy Colson wrote: > > so: select max(name), type from food group by type > works cuz we only get one name (the max name) back for each type. > > or: select name, type from food group by type, name > which in our example is kinda pointless, but still, give us the > distinct > items for "type, name". Thanks. I think I am beginning to see this. So, if there are more than one type for a given currency code and I do not select and group by type then the having count(whatever) = 1 means that these rows are not selected either. Is that 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
Re: [GENERAL] Help with join syntax sought
On Tue, May 19, 2009 16:41, Andy Colson wrote: > If your query above is getting you mostly what you want, just use it > as a derived table. > I lack the experience to understand what this means. If, as you suggest, I use a subquery as the expression to the main SELECT and for it I use the syntax that returns every distinct combination of base, quote, timestamp, and type, then what? Unless I am missing something then I still have too many rows for those currencies with more than one type. -- *** 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] Help with join syntax sought
I have a requirement to select the effective exchange rate for a number of currencies as of a specific date and time. The rates may come from several sources for the same currency. For some currencies the rate may be set infrequently. I have come close to getting this to work but cannot seem to get the last bit figured out. Thus my appeal for help. The table currency_exchange_rates has a composite unique index made up of: fxr.currency_code_base fxr.currency_code_quote fxr.effective_from fxr.currency_exchange_type Here is what I have so far: SELECT fxr.currency_code_base AS fx_base, fxr.currency_code_quote AS fx_quote, fxr.effective_from AS fx_date, fxr.currency_exchange_type AS fx_type, fxr.currency_exchange_rate AS fx_rate FROM currency_exchange_rates AS fxr LEFT OUTER JOIN currency_exchange_rates AS fxr_j ON fxr.currency_code_base = fxr_j.currency_code_base AND fxr.currency_code_quote= fxr_j.currency_code_quote AND fxr.currency_exchange_type = fxr_j.currency_exchange_type AND fxr.effective_from >= fxr_j.effective_from WHERE fxr.currency_code_base = 'CAD' AND fxr.effective_from <= current_timestamp GROUP BY fx_base, fxr.currency_code_quote, fx_date, fxr.currency_exchange_type, fx_rate HAVING COUNT(fxr.currency_code_quote) = 1 ORDER BY fx_base, fxr.currency_code_quote, fx_date DESC My problem with this version is that currencies with rates from more than one type show up for each type. This I do not want. I want only the most recent regardless of type. However, I need to display the type in the final report. Further, if I take the fxr.currency_exchange_type out of the SELECT columns, making the appropriate adjustments elsewhere, then all those currencies with more than one type are not selected at all. I am perplexed why I cannot select a column from the table without having to include it in the GROUP BY clause as well. Any help is welcomed. -- *** 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] Would like an explanation of these error messages.
I maintain a warm backup site at a co-location in another city. One of the things that gets moved there is a copy of the PostgreSQL backend for our company wiki and project management site. This is a simple process of dumping the database, rsyncing to the remote site and restoring. The relevant cron entries look like this: Source: ---> 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 <--- Destination: ---> 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 <--- This evidently works reliably. However, I have noticed these messages in the logwatch files of the destination host and I would like to have them explained to me. I would alos like any suggestions as to what I should be doing differently so as to avoid them altogether. ---> 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 <--- -- *** 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] No return from trigger function
On Wed, April 8, 2009 16:06, Tom Lane wrote: > "James B. Byrne" writes: >> I just need another set of eyes to see whatever it is that I am >> overlooking. > > The RETURN is inside the EXCEPTION clause. > > You really need two BEGINs here, one for the outer function body and > one for the exception block around the INSERT. > > regards, tom lane > So, something like: CREATE OR REPLACE FUNCTION hll_pg_fn_ident_insert() RETURNS TRIGGER AS $pg_fn$ -- ROW AFTER TRIGGER -- trigger passes identifier_type and _description -- received as ARGV[0] and ARGV[1] BEGIN BEGIN INSERT INTO identifiers(... ... EXCEPTION WHEN END; RETURN NULL: END; -- *** 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] No return from trigger function
On Wed, April 8, 2009 16:05, hubert depesz lubaczewski wrote: > On Wed, Apr 08, 2009 at 03:59:22PM -0400, James B. Byrne wrote: >> EXCEPTION >> WHEN unique_violation THEN >> -- NULL -- do nothing >> >> RETURN NULL; -- AFTER trigger results are ignored anyway >> END; > > exception is part of begin/exception/end; block > so you will need one more begin and one more end; > Does this mean that the example given on pg. 798 of the manual is in error, or have I misread it? -- Insert or update the summary row with the new values. <> LOOP UPDATE sales_summary_bytime SET amount_sold = amount_sold + delta_amount_sold, units_sold = units_sold + delta_units_sold, amount_cost = amount_cost + delta_amount_cost WHERE time_key = delta_time_key; EXIT insert_update WHEN found; BEGIN INSERT INTO sales_summary_bytime ( time_key, amount_sold, units_sold, amount_cost) VALUES ( delta_time_key, delta_amount_sold, delta_units_sold, delta_amount_cost ); EXIT insert_update; EXCEPTION WHEN UNIQUE_VIOLATION THEN -- do nothing END; END LOOP insert_update; RETURN NULL; END; -- *** 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] No return from trigger function
I just need another set of eyes to see whatever it is that I am
overlooking.
This is the function:
CREATE OR REPLACE FUNCTION hll_pg_fn_ident_insert()
RETURNS TRIGGER AS $pg_fn$
-- ROW AFTER TRIGGER
-- trigger passes identifier_type and _description
-- received as ARGV[0] and ARGV[1]
BEGIN
INSERT INTO identifiers(
entity_id,
identifier_type,
identifier_value,
identifier_description,
changed_at,
changed_by,
created_at,
created_by,
effective_from)
VALUES(
NEW.id,
TG_ARGV[0],
NEW.entity_common_name,
TG_ARGV[1],
current_timestamp,
'trigger',
current_timestamp,
'trigger',
current_timestamp);
-- Assume the INSERT fails because of a unique key violation,
-- (identifier_type + identifier_value + entity_id)
--
-- This does not matter since we only need ensure that this
-- alias exists, so handle the exception and return:
EXCEPTION
WHEN unique_violation THEN
-- NULL -- do nothing
RETURN NULL; -- AFTER trigger results are ignored anyway
END;
$pg_fn$ LANGUAGE plpgsql;
This is the trigger:
CREATE TRIGGER hll_pg_tr_entity_identifier_akna
AFTER INSERT OR UPDATE ON entities
FOR EACH ROW EXECUTE PROCEDURE hll_pg_fn_ident_insert(
"AKNA", "Common Name auto-insert");
I am getting this error:
PGError: ERROR: control reached end of trigger procedure
without RETURN
CONTEXT: PL/pgSQL function "hll_pg_fn_ident_insert"
: INSERT INTO "entities" ("entity_legal_name",
"entity_legal_name_key", "changed_by", "entity_common_name",
"created_by", "lock_version", "changed_at",
"entity_legal_form", "created_at") VALUES(E'My Entity Legal
Name', E'myentitylegalname', E'not available', E'my entity',
E'not available', 0, '2009-04-08 19:46:49', E'PERS',
'2009-04-08 19:46:49.446650')
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] INSERT or UPDATE TRIGGER
On Tue, April 7, 2009 16:07, Tom Lane wrote: > > You might find it more useful to add some elog(LOG) statements to > the trigger body. > Thank you again. I will go through section 44.2 tonight. -- *** 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] INSERT or UPDATE TRIGGER
On Tue, April 7, 2009 15:09, Tom Lane wrote: > > ALTER DATABASE foo SET log_min_messages = whatever; > > Note this will only affect subsequently-started sessions. Also, > if memory serves, you have to be superuser to set this particular > variable. Thanks. Am I correct to infer from the output this generates that log_min_messages = debug is primarily for developers of PG itself? I am poking in the dark here. What I want to do is to determine if the trigger is firing and whether the function works as intended. At the moment I am not seeing anything show up in the secondary table so I have done something wrong. Is there some way of getting PG to tell me what it is doing? -- *** 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] INSERT or UPDATE TRIGGER
I am testing the trigger function that I wrote. Is there a way to increase the logging detail level for just a single database instance? The manual indicates not, but just in case I am misreading things I am asking 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] INSERT or UPDATE TRIGGER
This is what I have come up with. Comments are welcomed. CREATE OR REPLACE FUNCTION hll_pg_fn_ident_insert() RETURNS TRIGGER AS $pg_fn$ -- ROW AFTER TRIGGER -- trigger passes identifier_type, _value and _description -- received as ARGV[0], ARGV[1] and ARGV[2] BEGIN INSERT INTO identifiers( entity_id, identifier_type, identifier_value, identifier_description) VALUES( NEW.id, TG_ARGV[0], TG.ARGV[1], TG_ARGV[2]); -- Assume the INSERT fails because of a unique key violation, -- (entity_id + identifier_type + identifier_value) -- -- This does not matter since we only need ensure that this -- alias exists, so handle the exception and return: EXCEPTION WHEN unique_violation THEN -- do nothing NULL; END; $pg_fn$ LANGUAGE plpgsql; COMMENT ON FUNCTION hll_pg_fn_ident_insert IS 'Used by entities trigger. Inserts a corresponding identifiers row.' CREATE TRIGGER hll_pg_tr_entity_identifier_akna AFTER INSERT OR UPDATE ON entities FOR EACH ROW EXECUTE PROCEDURE hll_pg_fn_identifier_insert( "AKNA", entities.entity_common_name, "Common Name auto-insert"); COMMENT ON TRIGGER hll_pg_tr_entity_identifier_akna IS 'Inserts an alias identifier for common name if one does not exist' -- *** 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] INSERT or UPDATE
On Mon, April 6, 2009 20:23, Dann Corbit wrote: > > If a transaction involves rows where some succeed and some fail, > all will roll back. If that is the desired behavior, or if all > operations are singleton, then you won't see any problems. > Do I understand correctly that this means that even if the function "handles" a failed insert then if the function occurs inside a transaction then that transaction fails and is rolled back 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] INSERT or UPDATE
On Mon, April 6, 2009 17:15, Dann Corbit wrote: > > The pedagogic solution for this type of problem is called merge. > The last I knew, PostgreSQL did not directly support merge. > So you can accomplish the same thing in two stages: > 1. Check for existence and perform an update if the key is present > 2. If the key is not present, then perform an insert. > > Again, this may or may not be the right thing to do. > Forgive my obtuseness, but what does the preliminary SELECT accomplish? When the trigger fires we already know whether or not the entities row existed previously, what we are deciding is how to handle the concurrent identifiers table entry. I initially thought along these lines (select insert/update depending on the return value) but I gradually realized that it did not matter whether the identifier row was already there or not. If it exists then an UNIQUE key constraint prevents duplicates. If it does not exist then the INSERT succeeds. The previous identifier associated with the original common name has to remain on file to allow lookups by former names. Thus, we never update an identifier row in this fashion. -- *** 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] INSERT or UPDATE
On Mon, April 6, 2009 17:00, Dann Corbit wrote:
.
>
> It is a difficult question.
>
> For instance, there are many possibilities when a collision occurs.
>
> I guess that for some collisions, sharing the name is OK.
>
I failed to explicitly state what the PK looked like.
entity_id(entities.id) +
identifier_type ('AKNA') +
identifier_value(entities.common_name)
There will only be a PK collision when we attempt to add a duplicate
common name for the same entity, which means it already exists and
does not need to be added again.
--
*** 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
