Re: [GENERAL] Character encoding problems
On 12/08/11 7:54 PM, Bruce Clay wrote: Is there a proper encoding type that I should use to load the word lists so they can be interoperable with the WordNet dataset that happily uses the UTF8 encoding? some of your input data may be in other encodings, not UTF8, for instance, LATIIN1. if you can identify these, and use SET CLIENT_ENCODING=... at the appropriate times, you should be able to import from the various data sources. otherwise, you might have to run the data through some sort of filter before you feed it to postgres, I dunno. I'm pretty sure 0x82 is not a valid code in UTF8. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] Hope for a new PostgreSQL era?
Le Fri, 09 Dec 2011 11:11:12 +0800, Craig Ringer ring...@ringerc.id.au a écrit : On 12/08/2011 08:27 PM, Simon Riggs wrote: On Thu, Dec 8, 2011 at 11:24 AM, Craig Ringerring...@ringerc.id.au wrote: Areas in which Pg seems significantly less capable include: Please can you explain the features Oracle has in these area, I'm not clear. Thanks. Marc has, as I was hoping, done so much better than I could. Most of what I know is 2nd hand from Oracle users - I'm not one myself. It's interesting to see the view that the resource manager for query and user prioritisation is hard to use in practice. That's not something I'd heard before, but I can't say I'm entirely surprised given how complicated problems around lock management and priority inversion are to get right even in a system where there *aren't* free-form dynamic user-defined queries running. The complexity, at least for me, came from the user interface (at least a dozen of stored procedures with a complex syntax) to set up and monitor the resource manager. I don't think it manages the priority inversion problems, just CPU priorities. I asked the Oracle trainer, who wasn't sure either :) -- 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]
FWIW, a couple of months ago the anti-spam mail filters on our server started to reject anything yahoo (including loads of legit stuff, obviously). And I still see a lot of messages announcing that Ive just been subscribed to this or that yahoo discussion group. However, this is all OT, I suppose what we need here is just a way to signal spammers to the admin, is there any? Bèrto On 9 December 2011 06:13, Tom Lane t...@sss.pgh.pa.us wrote: Joshua D. Drake j...@commandprompt.com writes: On 12/08/2011 03:12 PM, Raymond O'Donnell wrote: Just wondering, and without intending to cast any aspersions on the poster - is this spam or legit? I didn't take the risk of actually clicking it... It is not legit in any way. Lately we've been seeing a lot of link-spam with just a link in the body, generally no subject line, and the real tip-off is that it's addressed To: both pgsql-general (or another of our lists) and half a dozen random other addresses. I assume these are coming from addresses that are actually subscribed to our lists, because otherwise the moderators should've rejected them. Probably somebody's found a way to break into large numbers of yahoo and google mail accounts and spam from them to all their address book entries ... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- == If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music.
Re: [GENERAL] Hope for a new PostgreSQL era?
On Thu, Dec 8, 2011 at 3:11 PM, Marc Cousin cousinm...@gmail.com wrote: Le Thu, 8 Dec 2011 12:27:22 +, Simon Riggs si...@2ndquadrant.com a écrit : On Thu, Dec 8, 2011 at 11:24 AM, Craig Ringer ring...@ringerc.id.au wrote: Areas in which Pg seems significantly less capable include: Please can you explain the features Oracle has in these area, I'm not clear. Thanks. Maybe I can answer from my own Oracle experience. I hope it will be what Craig had in mind :) - admission control, queuing and resource limiting to optimally load a machine. Some limited level is possible with external pooling, but only by limiting concurrent workers. Oracle has natively two ways of handling inbound connections: - Dedicated, which is very similar to the PostgreSQL way of accepting connections: accept(), fork() and so on - Shared, which is based on processes listening and handling the connections (called dispatchers) and processes doing the real work (called workers, obviously). All of this works internally with some sort of queuing and storing results in shared memory (I don't remember the details of it) The advantage of this second architecture being of course that you can't have more than N workers hitting your database simultaneously. So it's easier to keep the load on the server to a reasonable value. - prioritisation of queries or users. It's hard to say prefer this query over this one, give it more resources or user A's work always preempts user B's in Pg. It's called the resource manager in Oracle. You define 'resource plans', 'consumer groups', etc… and you get some sort of QoS for your queries. It's mostly about CPU resource allocation if I remember correctly (I never used it, except during training :) ) Being able of changing the backend's nice level may do something similar I guess. I don't think Oracle's resource manager solves the priority inversion due to locking in the database, but I'm not sure of it. Thanks, sounds interesting. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Why is cast array integer[] -- text[] is not immutable.
Hi I'm struggling to understand why this casts is not immutable: integer[]::text[] text[]::integer[] The following are all immutable: integer::text text::integer integer[]::float[] integer::float I hit on this while trying to make a gin index which cast from one to the other. Why does the encapsulation of an array suddenly make this not immutable? Thanks -- 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] Hope for a new PostgreSQL era?
Am 08.12.2011 19:54, schrieb John R Pierce: On 12/08/11 10:14 AM, Joshua D. Drake wrote: - shared-storage clustering. Dunno if anyone still cares about this one though. This one seems to be moving into the legacy category over the next 3-5 years. um, I believe this is referring to Oracle RAC clustering, not HA active/standby. I seriously doubt Oracle is dropping RAC. Oracle Exadata reqires RAC. Therefore I also don't think, they will drop this ;-) -- Andreas 'ads' Scherbaum German PostgreSQL User Group European PostgreSQL User Group - Board of Directors Volunteer Regional Contact, Germany - PostgreSQL Project -- 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] Hope for a new PostgreSQL era?
On Thu, Dec 8, 2011 at 2:54 PM, Merlin Moncure mmonc...@gmail.com wrote: On Wed, Dec 7, 2011 at 8:52 PM, Rodrigo E. De León Plicet rdele...@gmail.com wrote: http://www.dbms2.com/2011/11/23/hope-for-a-new-postgresql-era/ Some of the points mentioned: - MySQL is narrowing the gap, but PostgreSQL is still ahead of MySQL in some ways. (Database extensibility if nothing else.) There is simply no comparing mysql's backend programming features with those of postgres. Postgres is a development platform in a box, mysql is not. A key point, I think, but not just as a comparison against other RDBMS. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Question regarding authentication/login
Hello, I have installed postgres version 8.4.9 from the debian repository. I set up a username and password, and was able to create my tables and add information to the database from a java application running through a remote SSH tunnel. Then I moved this same command line program onto the same server as the database resides : when I create tables from this location I can only access them from this local machine: I can use psql -U user dbname (same login and pass as the remote connection) and i canselect * from users; and it shows all the entries just fine. But if I try to connect using the same login and password through a remote SSH tunnel, I can not see any of the tables created from the CLI on the server... If I create the tables from the remote location I can query them fine. The exact error message is : ERROR: relation users does not exist (Either from pgAdmin GUI, or from the command line interface that comes with pgAdmin ) Am I misunderstanding something fundamental about user authentication? How does postgres distinguish localhost connections from SSH tunneled connections? Is it possible that somehow connecting form a local linux-user account is creating hidden tables within my otherwise remotely accessable database? Thank you
Re: [GENERAL] Database system identifier via SELECT?
Yeah, it would be easy enough to write a custom extension to do it. I was hoping for something built-in so I wouldn't require a pre-req extension be installed on all servers by the superusersysadmins tend to resist making such changes. But oh well what you gotta do you gotta dothanks guys. FYI this isn't the first time it's been asked for... http://archives.postgresql.org/pgsql-sql/2007-07/msg00045.php ...first time in 4 years tho :P On Thu, Dec 8, 2011 at 3:09 PM, Safari Code safaric...@gmail.com wrote: You can get the database system identifier from the OS shell as part of the control data: pg_controldata /Library/PostgreSQL/9.1/data Here, '/Library/PostgreSQL/9.1/data' is my data directory on os x; replace it with your own data directory. From there, you can isolate the database system identifier with grep: pg_controldata /Library/PostgreSQL/9.1/data | grep system identifier This is not the same as calling a function within a SELECT statement, but using the shell command above, one could easily write a function that returns the database system identifier as a string in a SQL query. I hope this solves the problem. On Thu, Dec 8, 2011 at 4:57 PM, Scott Mead sco...@openscg.com wrote: On Thu, Dec 8, 2011 at 4:27 PM, Bruce Momjian br...@momjian.us wrote: Joshua D. Drake wrote: On 12/08/2011 12:57 PM, Bruce Momjian wrote: Chris Redekop wrote: Is there any way to get the database system identifier via a select statement? I have a primary/secondary async replication setup, and I'd like be able to verify from the client side that the provided primary and secondary connection strings do in fact refer to the same data set... Wow, that is a reasonable thing to want available via SQL, but I can't see a way to get to it. The only method I can suggest is to write a server-side C function that calls GetSystemIdentifier(). select inet_server_addr()? --Scott This seems like something we should have in core, don't you think? Yeah, kind of, except this is the first request we ever got for this. The identifier is passed as part of streaming replication, so maybe it will be needed more in the future. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Question regarding authentication/login
On Thursday, December 08, 2011 1:40:08 pm Chris Deadlock wrote: Hello, I have installed postgres version 8.4.9 from the debian repository. I set up a username and password, and was able to create my tables and add information to the database from a java application running through a remote SSH tunnel. Then I moved this same command line program onto the same server as the database resides : when I create tables from this location I can only access them from this local machine: I can use psql -U user dbname (same login and pass as the remote connection) and i canselect * from users; and it shows all the entries just fine. But if I try to connect using the same login and password through a remote SSH tunnel, I can not see any of the tables created from the CLI on the server... If I create the tables from the remote location I can query them fine. The exact error message is : ERROR: relation users does not exist (Either from pgAdmin GUI, or from the command line interface that comes with pgAdmin ) Am I misunderstanding something fundamental about user authentication? How does postgres distinguish localhost connections from SSH tunneled connections? See here: http://www.postgresql.org/docs/8.4/interactive/auth-pg-hba-conf.html Is it possible that somehow connecting form a local linux-user account is creating hidden tables within my otherwise remotely accessable database? First question, are you sure you are connecting to same database in both the remote and local case? If the answer to above is yes, then it sounds like a search_path issue. To confirm, when trying to select from users in the remote case use the full schema qualified name for table. i.e some_schema.users. Thank you -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to configure the connection timeout in PostgreSQL 8.3
Hi, I'm using PostgreSQL 8.3 and I need to reduce the timeout. How can I configure the connection timeout? Best Regards, -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why is cast array integer[] -- text[] is not immutable.
Phil Couling coul...@gmail.com writes: I'm struggling to understand why this casts is not immutable: integer[]::text[] text[]::integer[] Because it's implemented via array_out/array_in rather than any more direct method, and those are marked stable because they potentially invoke non-immutable element I/O functions. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to configure the connection timeout in PostgreSQL 8.3
Am 09.12.2011 16:02, schrieb Andre Lopes: I'm using PostgreSQL 8.3 and I need to reduce the timeout. How can I configure the connection timeout? Which connection timeout you like to change? Most likely this should be an option you can change on your client. Cheers, Frank -- 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] Why is cast array integer[] -- text[] is not immutable.
Thanks I'm having trouble finding any reference to array_out and array_in in the documentation. Is there a way to set a different cast for an array? Regards On 9 December 2011 15:09, Tom Lane t...@sss.pgh.pa.us wrote: Phil Couling coul...@gmail.com writes: I'm struggling to understand why this casts is not immutable: integer[]::text[] text[]::integer[] Because it's implemented via array_out/array_in rather than any more direct method, and those are marked stable because they potentially invoke non-immutable element I/O functions. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why is cast array integer[] -- text[] is not immutable.
Phil Couling coul...@gmail.com writes: Is there a way to set a different cast for an array? I think it should work to declare a cast explicitly for the particular case. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OT DBA type question - GRANT PRIVILEGE
On Thu, December 8, 2011 20:23, Craig Ringer wrote: On Thu, December 8, 2011 17:28, Andy Colson wrote: These are the only replies I received. Am I to conclude that most of the people on the list do not use GRANT PRIVILEGE to implement anything more than the minimal authorization scheme required to obtain access for anyone? Nil reports, as in: Generally, we do not use PostgreSQL's GRANT PRIVILEGE to implement detailed user ACLs, would be most welcome. If instead you are using GRANT PRIVILEGE and have not responded, a simple We (often, occasionally, seldom) use detailed GRANT PRIVILEGE based user ACLs is sufficient. I really want to get a sense of how prevalent using GRANT PRIVILEGE, beyond the minimum required, is. And this seems like a very good place to discover it. Again, please reply off-list if you wish. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OT DBA type question - GRANT PRIVILEGE
On Fri, December 9, 2011 12:13, James B. Byrne wrote: Just to clarify the question. What I am asking basically comes down to if separate user ids are added for most individuals that access the database or not. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OT DBA type question - GRANT PRIVILEGE
On Fri, Dec 9, 2011 at 10:43 AM, James B. Byrne byrn...@harte-lyne.ca wrote: On Fri, December 9, 2011 12:13, James B. Byrne wrote: Just to clarify the question. What I am asking basically comes down to if separate user ids are added for most individuals that access the database or not. Most of the time I create a role, grant / revoke privileges there, and then grant that role to individuals as needed. I almost never grant / revoke privileges to an individual -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Is the a magic number for WAL files
I would like the file command to tell me something other than data, yes even though I can tell by the name (and the directory of course). Hoping someone has something I can slip into /usr/share/misc/magic.mgc or that directory. Along the same lines, what info is embedded in the file name? I see that the second non-zero recently went from 2 to 3. Significance? 0001003000CF ^ --| -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OT DBA type question - GRANT PRIVILEGE
On Fri, Dec 09, 2011 at 12:13:59PM -0500, James B. Byrne wrote: I really want to get a sense of how prevalent using GRANT PRIVILEGE, beyond the minimum required, is. And this seems like a very good place to discover it. Again, please reply off-list if you wish. I used it this year in a system that was designed to mimic a complicated Oracle mutli-user set up. I used a number of schemas, the search_path, and a lot of GRANTs to make everything work reliably in the cases where there was shared data across the users. It seemed to work for me. A -- Andrew Sullivan a...@crankycanuck.ca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is the a magic number for WAL files
On 9 December 2011 18:46, Rob Sargent robjsarg...@gmail.com wrote: Along the same lines, what info is embedded in the file name? I see that the second non-zero recently went from 2 to 3. Significance? 0001003000CF ^ --| The WAL file name consists of timeline, segment set/segment block and segment, Once the segment (the last 8 characters of the file name) reaches 00FE, the next file will have a segment but characters 9-16 will increment their value to reflect this wraparound. So it's not any more significant that 1 added to 99 results in it becoming 00 with a 1 before it. -- Thom -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OT DBA type question - GRANT PRIVILEGE
On Fri, Dec 09, 2011 at 12:13:59PM -0500, James B. Byrne wrote: I really want to get a sense of how prevalent using GRANT PRIVILEGE, beyond the minimum required, is. And this seems like a very good place to discover it. Again, please reply off-list if you wish. I used it this year in a system that was designed to mimic a complicated Oracle mutli-user set up. I used a number of schemas, the search_path, and a lot of GRANTs to make everything work reliably in the cases where there was shared data across the users. It seemed to work for me. A -- Andrew Sullivan a...@anvilwalrusden.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is the a magic number for WAL files
On 9 December 2011 18:46, Rob Sargent robjsarg...@gmail.com wrote: I would like the file command to tell me something other than data, yes even though I can tell by the name (and the directory of course). Hoping someone has something I can slip into /usr/share/misc/magic.mgc or that directory. You mean something like this?: /* * Each page of XLOG file has a header like this: */ #define XLOG_PAGE_MAGIC 0xD068 /* can be used as WAL version indicator */ Obviously that isn't stable. -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Why does aggregate query allow select of non-group by or aggregate values?
CREATE TABLE people(
id serial PRIMARY KEY,
name varchar NOT NULL
);
INSERT INTO people(name) VALUES('Adam'), ('Adam'), ('Adam'), ('Bill'),
('Sam'), ('Joe'), ('Joe');
SELECT name, count(*), random()
FROM people
GROUP BY name;
I would expect this query to cause an error because of random(). I ran
into this using an array produced by a subquery as a column in the
select of an aggregate query, but I was able to boil it down to this
contrived example. Shouldn't any expression that is not in the group by
or an aggregate function be rejected?
What am I not understanding?
Thanks.
--
Jack Christensen
ja...@hylesanderson.edu
--
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] Why does aggregate query allow select of non-group by or aggregate values?
On Fri, Dec 9, 2011 at 5:48 PM, Jack Christensen ja...@hylesanderson.eduwrote:
CREATE TABLE people(
id serial PRIMARY KEY,
name varchar NOT NULL
);
INSERT INTO people(name) VALUES('Adam'), ('Adam'), ('Adam'), ('Bill'),
('Sam'), ('Joe'), ('Joe');
SELECT name, count(*), random()
FROM people
GROUP BY name;
I would expect this query to cause an error because of random(). I ran
into this using an array produced by a subquery as a column in the select
of an aggregate query, but I was able to boil it down to this contrived
example. Shouldn't any expression that is not in the group by or an
aggregate function be rejected?
What am I not understanding?
Thanks.
--
Jack Christensen
ja...@hylesanderson.edu
I don't know the answer, but I would guess that as random() is not known
before hand , it has no reason being in the grouped by.
Re: [GENERAL] Why does aggregate query allow select of non-group by or aggregate values?
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Jack Christensen
Sent: Friday, December 09, 2011 5:48 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Why does aggregate query allow select of non-group by or
aggregate values?
CREATE TABLE people(
id serial PRIMARY KEY,
name varchar NOT NULL
);
INSERT INTO people(name) VALUES('Adam'), ('Adam'), ('Adam'), ('Bill'),
('Sam'), ('Joe'), ('Joe');
SELECT name, count(*), random()
FROM people
GROUP BY name;
I would expect this query to cause an error because of random(). I ran into
this using an array produced by a subquery as a column in the select of an
aggregate query, but I was able to boil it down to this contrived example.
Shouldn't any expression that is not in the group by or an aggregate
function be rejected?
What am I not understanding?
Thanks.
---
Functions are evaluated once for each row that it generated by the
surrounding query. This is particularly useful if the function in question
takes an aggregate as an input:
SELECT col1, array_processing_function( ARRAY_AGG( col2 ) )
FROM table
GROUP BY col1;
Without this particular behavior you would need to sub-query.
From a layman's perspective the reason why you cannot use non-aggregates
outside of GROUP BY it that it is ambiguous as to what value to output; with
an uncorrelated function call that is not the case.
David J.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why does aggregate query allow select of non-group by or aggregate values?
On 12/09/2011 02:48 PM, Jack Christensen wrote:
CREATE TABLE people(
id serial PRIMARY KEY,
name varchar NOT NULL
);
INSERT INTO people(name) VALUES('Adam'), ('Adam'), ('Adam'), ('Bill'),
('Sam'), ('Joe'), ('Joe');
SELECT name, count(*), random()
FROM people
GROUP BY name;
I would expect this query to cause an error because of random(). I ran
into this using an array produced by a subquery as a column in the
select of an aggregate query, but I was able to boil it down to this
contrived example. Shouldn't any expression that is not in the group by
or an aggregate function be rejected?
What am I not understanding?
http://www.postgresql.org/docs/9.0/interactive/sql-select.html#SQL-GROUPBY
Aggregate functions, if any are used, are computed across all rows
making up each group, producing a separate value for each group (whereas
without GROUP BY, an aggregate produces a single value computed across
all the selected rows). When GROUP BY is present, it is not valid for
the SELECT list expressions to refer to ungrouped columns except within
aggregate functions, since there would be more than one possible value
to return for an ungrouped column.
My guess, random() does not refer to a column, so it falls outside the
above criteria.
Thanks.
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Hope for a new PostgreSQL era?
On 12/08/2011 09:48 AM, Satoshi Nagayasu wrote: For examples, I've been working on investigating PostgreSQL LWLock behaviors precisely for a few weeks, and it could not be obtained within PostgreSQL itself, therefore, I picked up SystemTap. However, SystemTap could not be used in a production system, because it often kills the target processes. :( How can I observe LWLocks in the production system? I decided about a year ago that further work on using SystemTap was a black hole: time goes in, nothing really usable on any production server seems to come out. It can be useful for collecting data in a developer context. But the sort of problems people are more interested in all involve why is the production server doing this?, and as you've also discovered the only reasonable answer so far doesn't involve SystemTap; it involves DTrace and either Solaris or FreeBSD (or Mac OS, for smaller server hardware deployments). Since those platforms are problematic to run database servers on in many cases, that doesn't help very much. I'm planning to put that instrumentation into the database directly, which is what people with Oracle background are asking for. There are two underlying low-level problems to solve before even starting that: -How can the overhead of collecting the timing data be kept down? It's really high in some places. This is being worked out right now on pgsql-hackers, see Timing overhead and Linux clock sources -How do you log the potentially large amount of data collected without killing server performance? Initial discussions also happening right now, see logging in high performance systems. I feel this will increasingly be the top blocker for performance sensitive deployments in the coming year, people used to having these tools in Oracle cannot imagine how they would operate without them. One of my big pictures goals is have this available as a compile-time option starting in PostgreSQL 9.3 in 2013, piggybacked off the existing DTrace support. And the earlier the better--since many migrations have a long lead time, just knowing it's coming in the next version would be good enough for some people who are blocked right now to start working on theirs. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Question regarding authentication/login
On 12/09/2011 10:35 PM, Adrian Klaver wrote: First question, are you sure you are connecting to same database in both the remote and local case? It strikes me that this is another use case for being able to get the system identifier from SQL :-) -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
