Re: [GENERAL] log_statement and syslog severity
On Wed, Mar 10, 2010 at 8:51 AM, Bruce Momjian wrote: Greg Sabino Mullane wrote: Bruce replied: ... >> This means that, even using syslog as a destination, it's not possible for >> me to filter statements without some sort of log-text parsing, which I'd >> prefer to avoid on effort, performance and data-integrity grounds. > Our logging system is very flexible, but not work-free on the user end. > I don't see us changing things in that area. Bruce, that's a little harsh, I think the original poster has a legitimate request. Personally, I'd love to be able to split the logs on various things, the most important to me being durations and per-database. I looked at the code about a year ago to see how hard this would be and found it non-trivial (for me), as we're really assuming hard things go to a single filehandle. It's definitely an area for improvement, and should be a TODO if not already. This issue has been discussed and I think the community conclusion was that this should not be done by the database but rather by external tools. I think I was giving an accurate portrayal of the odds of this getting added. I do not think there is enough support for this to be a TODO item. How do you plug in this external tool? Installing a filter on stderr doesn't play well with packaged versions of PostgreSQL and probably not Windows either. You also don't get easily machine readable output. It might be possible to trick csvlog to log to a static filename, and perhaps substituting that with a named pipe might work (under unix at least). syslog doesn't give you easily machine readable output. I'm not sure how syslog implementations handle high load (our sysadmins won't use it, so I haven't investigated this further). I need to be analyzing log messages from PostgreSQL in real time, so am starting to investigate solutions. It seems painful, which would be avoidable for future generations if PostgreSQL could spawn a subprocess and send log messages to that in a machine readable format. Perhaps useful filters might start to exist and eventually end up in contrib or core. -- Stuart Bishop http://www.stuartbishop.net/ signature.asc Description: OpenPGP digital signature
Re: [GENERAL] log_statement and syslog severity
2010/3/10 Stuart Bishop : > > > On Wed, Mar 10, 2010 at 8:51 AM, Bruce Momjian wrote: >> >> Greg Sabino Mullane wrote: >>> >>> Bruce replied: >>> ... >>> >> This means that, even using syslog as a destination, it's not possible >>> >> for >>> >> me to filter statements without some sort of log-text parsing, which I'd >>> >> prefer to avoid on effort, performance and data-integrity grounds. >>> >>> > Our logging system is very flexible, but not work-free on the user end. >>> > I don't see us changing things in that area. >>> >>> Bruce, that's a little harsh, I think the original poster has a legitimate >>> request. Personally, I'd love to be able to split the logs on various >>> things, >>> the most important to me being durations and per-database. I looked at the >>> code about a year ago to see how hard this would be and found it non-trivial >>> (for me), as we're really assuming hard things go to a single filehandle. >>> It's definitely an area for improvement, and should be a TODO if not >>> already. >> >> This issue has been discussed and I think the community conclusion was >> that this should not be done by the database but rather by external >> tools. I think I was giving an accurate portrayal of the odds of this >> getting added. I do not think there is enough support for this to be a >> TODO item. > > How do you plug in this external tool? > > Installing a filter on stderr doesn't play well with packaged versions of > PostgreSQL and probably not Windows either. You also don't get easily machine > readable output. > > It might be possible to trick csvlog to log to a static filename, and perhaps > substituting that with a named pipe might work (under unix at least). > > syslog doesn't give you easily machine readable output. I'm not sure how > syslog implementations handle high load (our sysadmins won't use it, so I > haven't investigated this further). > > I need to be analyzing log messages from PostgreSQL in real time, so am > starting to investigate solutions. It seems painful, which would be avoidable > for future generations if PostgreSQL could spawn a subprocess and send log > messages to that in a machine readable format. Perhaps useful filters might > start to exist and eventually end up in contrib or core. We already have a subprocess that takes this, and if we use CSV format it's machine readable. I had a patch sometime back last autumn that did a fairly major restructuring to allow some of this kind of refactoring, but it was rejected (on reasonable grounds). My next thought around that was to add a "pipe" style log_destination to just make it possible to hand things off to a different process. The reasonable way to do it would be to send it out in CSV format. It'll cause a fairly large amount of parsing overhead and such compared to a builtin solution, but it'll give the flexibility to develop all such filters outside of core. But that's all 9.1 material, of course. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to? Timestamp with timezone.
Hi, Thanks for the reply's. [code] ALTER DATABASE foo SET timezone TO 'someval' ALTER ROLE bar SET timezone TO 'someval' [/code] I need to alter only the Timezone of the database OR I need also to alter the Role? Best Regards, On Wed, Mar 10, 2010 at 1:06 AM, Alvaro Herrera wrote: > Andre Lopes escribió: > > It is possible to do this in a shared database server? > > You can also do > > ALTER DATABASE foo SET timezone TO 'someval' > ALTER ROLE bar SET timezone TO 'someval' > > -- > Alvaro Herrera > http://www.CommandPrompt.com/ > The PostgreSQL Company - Command Prompt, Inc. >
Re: [GENERAL] Urgent help needed- alias name in update statement
Venkat wrote: > In postgre, when i am trying to give alias name in update > statement like below - > > - > update mytable x > set x.name = 'asdf' > where x.no = 1 > --- > > > is giving error - mytable is not having col x. > > We have migrated code from oracle to postgre 8.4. Is there > any solution for this. > (functions were compiled without any compilation errors - now > when we are trying to run these we are getting problems) The name is PostgreSQL or Postgres. I looked at the syntax of the UPDATE statement as specified by the SQL standard, and it says in Part 2, chapters 14.11 and 14.12, that PostgreSQL is behaving in the standard-conforming way. If you use nonstandard SQL extensions of a database vendor, portability will suffer, which is neither Oracle's nor PostgreSQL's fault. I guess you'll have to rewrite those UPDATE statements. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] make available C extensions to others
I've finished to write an extension to manipulate tsvectors and tsquery in C. I think it could be useful for someone else and I think I may take advantage at someone else looking at the code too. What would be the right place where to publish the code and how? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- 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] make available C extensions to others
Ivan Sergio Borgonovo writes: > I've finished to write an extension to manipulate tsvectors and > tsquery in C. > > I think it could be useful for someone else and I think I may take > advantage at someone else looking at the code too. > > What would be the right place where to publish the code and how? Create a pgfoundry project, and publish some doc and pointers at the public URL for visibility: mycode.projects.postgresql.org. Then I'd say host the code elsewhere, unless you're fond of CVS. I picked up github, but any hosting would do I guess. Same for mailing lists and all. I think that's the current advice. Regards, -- dim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] regexp_replace puzzle
I've got a problem with regexp_replace which I could reduce to the following:
CREATE FUNCTION digest(text, text) RETURNS bytea
LANGUAGE c IMMUTABLE STRICT
AS '$libdir/pgcrypto', 'pg_digest';
CREATE FUNCTION sha224enc(text) RETURNS text AS $$
BEGIN
RAISE WARNING 'arg=»%«', $1;
RETURN encode(digest($1, 'sha224'), 'hex');
END;
$$ LANGUAGE plpgsql IMMUTABLE;
CREATE TABLE t1 (
id serial NOT NULL,
val text NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO t1 (val) VALUES ('d11');
INSERT INTO t1 (val) VALUES ('xd22');
INSERT INTO t1 (val) VALUES ('x d33');
SELECT val,
regexp_replace(val,
'^(.*\W)?(C\d{7}|[DI]\d{6}|S\d{10})(\W.*)?$',
'\1' || '»\2«='|| sha224enc('\2') || '\3', 'i')
FROM t1
WHERE val ~*
'^(.*\W)?(C\d{7}|[DI]\d{6}|S\d{10})(\W.*)?$';
(I want to replace patterns within a string by their SHA-224 hash.)
However, when I run this example I get:
WARNING: arg=»\2«
val|regexp_replace
---+--
d11 |
»d11«=da2c99be8170ce02b04ee7d90877ae9d86fea467abb973c74c708b27
x d33 | x
»d33«=da2c99be8170ce02b04ee7d90877ae9d86fea467abb973c74c708b27
(2 rows)
i.e. the first '\2' gets properly expanded by the second paren match,
but the second '\2' doesn't get expanded.
What am I overlooking?
--
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? Timestamp with timezone.
Andre Lopes escribió: > Hi, > > Thanks for the reply's. > > [code] > ALTER DATABASE foo SET timezone TO 'someval' > ALTER ROLE bar SET timezone TO 'someval' > [/code] > > I need to alter only the Timezone of the database OR I need also to alter > the Role? One of them suffices. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] regexp_replace puzzle
2010/3/10 Harald Fuchs :
> I've got a problem with regexp_replace which I could reduce to the following:
>
> CREATE FUNCTION digest(text, text) RETURNS bytea
> LANGUAGE c IMMUTABLE STRICT
> AS '$libdir/pgcrypto', 'pg_digest';
>
> CREATE FUNCTION sha224enc(text) RETURNS text AS $$
> BEGIN
> RAISE WARNING 'arg=»%«', $1;
> RETURN encode(digest($1, 'sha224'), 'hex');
> END;
> $$ LANGUAGE plpgsql IMMUTABLE;
>
> CREATE TABLE t1 (
> id serial NOT NULL,
> val text NOT NULL,
> PRIMARY KEY (id)
> );
>
> INSERT INTO t1 (val) VALUES ('d11');
> INSERT INTO t1 (val) VALUES ('xd22');
> INSERT INTO t1 (val) VALUES ('x d33');
>
> SELECT val,
> regexp_replace(val,
> '^(.*\W)?(C\d{7}|[DI]\d{6}|S\d{10})(\W.*)?$',
> '\1' || '»\2«='|| sha224enc('\2') || '\3', 'i')
> FROM t1
> WHERE val ~*
> '^(.*\W)?(C\d{7}|[DI]\d{6}|S\d{10})(\W.*)?$';
>
> (I want to replace patterns within a string by their SHA-224 hash.)
> However, when I run this example I get:
>
> WARNING: arg=»\2«
> val | regexp_replace
> ---+--
> d11 |
> »d11«=da2c99be8170ce02b04ee7d90877ae9d86fea467abb973c74c708b27
> x d33 | x
> »d33«=da2c99be8170ce02b04ee7d90877ae9d86fea467abb973c74c708b27
> (2 rows)
>
> i.e. the first '\2' gets properly expanded by the second paren match,
> but the second '\2' doesn't get expanded.
>
> What am I overlooking?
>
Use g flag.
"Flag g causes the function to find each match in the string, not only
the first one, and return a row for each such match." [1]
Osvaldo
[1]
http://www.postgresql.org/docs/current/interactive/functions-matching.html#FUNCTIONS-POSIX-REGEXP
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Licence
Hi Everyone, Can someone please confirm that the PostgreSQL licence allow commercial distribution (with a fee charged)? I am developing a proprietary (i.e. non-free) solution in Java, and wish to use PostgreSQL as the backend database. We wish to ship the server with our software, as well as use the JDBC driver included in our java solution. I know the PostgreSQL licence is "based" on the BSD licence, however the line which says "without fee" rings alarm bells, even though I think it means that "you don't have ot pay anything to the PostgreSQL developers" rather than "if you distribute, you must not charge a fee" Is it just a matter of placing the PostgreSQL licence text in the "About" box of the solution, as well as in the paper/PDF manual? Thanks Jonny
Re: [GENERAL] Licence
On 10 March 2010 14:49, Jonathan Tripathy wrote: > Hi Everyone, > > Can someone please confirm that the PostgreSQL licence allow commercial > distribution (with a fee charged)? > > I am developing a proprietary (i.e. non-free) solution in Java, and wish to > use PostgreSQL as the backend database. We wish to ship the server with our > software, as well as use the JDBC driver included in our java solution. > > I know the PostgreSQL licence is "based" on the BSD licence, however the > line which says "without fee" rings alarm bells, even though I think it > means that "you don't have ot pay anything to the PostgreSQL developers" > rather than "if you distribute, you must not charge a fee" > > Is it just a matter of placing the PostgreSQL licence text in the "About" > box of the solution, as well as in the paper/PDF manual? > > Thanks > > Jonny > You may do with PostgreSQL what you wish. Change it, sell it, do anything, and there are no charges or fees to pay under any condition. Here's the key paragraph: "Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies." Regards Thom
Re: [GENERAL] Licence
In response to "Jonathan Tripathy" : > > I know the PostgreSQL licence is "based" on the BSD licence, however the line > which says "without fee" rings alarm bells, even though I think it means that > "you don't have ot pay anything to the PostgreSQL developers" rather than "if > you distribute, you must not charge a fee" The "without fee" part means that you don't owe anyone a fee for doing so. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Statement Triggers
Hi, Can anyone point me at a comprehensive example of statement (as opposed to row) triggers? I've googled it and looked through the documentation, but couldn't find a complete example relevant to what I'm trying to do. Specifically, what features of the SQL statement that triggered the event are available to the function invoked by the trigger? Say I wanted to write all INSERT statements executed on a table into a log file. How would I access the original statement in the triggered function? If I cannot access the statement itself, what information is available for statement based triggers? Thanks. Gordan -- 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] log_statement and syslog severity
On Mar 10, 2010, at 12:15 AM, Stuart Bishop wrote: > syslog doesn't give you easily machine readable output. I'm not sure how > syslog implementations handle high load (our sysadmins won't use it, so I > haven't investigated this further). Have you looked into syslog-ng? I believe it does just this sort of thing for you. -- 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] regexp_replace puzzle
On Wed, 10 Mar 2010 13:41:54 +0100, Harald Fuchs wrote about [GENERAL]
regexp_replace puzzle:
[snip]
> SELECT val,
> regexp_replace(val,
> '^(.*\W)?(C\d{7}|[DI]\d{6}|S\d{10})(\W.*)?$',
> '\1' || '»\2«='|| sha224enc('\2') || '\3', 'i')
[snip]
>i.e. the first '\2' gets properly expanded by the second paren match,
>but the second '\2' doesn't get expanded.
The second instance of '\2' is first passed to sha224enc(), then that
function's result is passed to regexp_replace.
--
Regards,
Dave [RLU #314465]
===
david.w.n...@ntlworld.com (David W Noon)
===
--
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? Timestamp with timezone.
Andre Lopes wrote: [code] ALTER DATABASE foo SET timezone TO 'someval' ALTER ROLE bar SET timezone TO 'someval' [/code] I need to alter only the Timezone of the database OR I need also to alter the Role? Timestamp and timezone handling in PostgreSQL is very powerful. Think of it as a hierarchy. The default timezone is that of my database cluster (say America/Los_Angeles). But say I maintain a database for someone in New York who wants to see things in their zone. I can override the default for that one database with: ALTER DATABASE newyorkdb SET timezone TO 'America/New_York'; Now they have a user, Anwyn, who telecommutes from Wales so: ALTER ROLE anwyn SET timezone TO 'WET'; makes her default timezone Western European Time regardless of the server and database settings. But when she runs reports for her customer in Ethiopia she overrides all of the above with an explicit: SET timezone to 'Africa/Addis_Ababa'; (The above setting remains in effect for the current session unless reset with: SET timezone to DEFAULT;) Her customer, as customers do, has a special request and wants the report to show the event times in the time zone of both their home office in Ethiopia and their branch office in Tokyo. No problem: SELECT event_time, event_time at time zone 'Asia/Tokyo' as tokyo_event, ...FROM ...; You can get a list of time zones with: SELECT * FROM pg_timezone_names; Cheers, Steve -- 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] Licence
Bill Moran wrote: In response to "Jonathan Tripathy" : I know the PostgreSQL licence is "based" on the BSD licence, however the line which says "without fee" rings alarm bells, even though I think it means that "you don't have ot pay anything to the PostgreSQL developers" rather than "if you distribute, you must not charge a fee" The "without fee" part means that you don't owe anyone a fee for doing so. We all know that, but the wording certainly is ambiguous and could be interpreted either way. Reminds me of Ed Asner in the old "Remenber, you can't put too much water in a nuclear reactor." nuke-plant retiree sketch on "Saturday Night Live." (When he left, they argued about the interpretation and eventually decided to drain the reactor. Final line to waitress on the beach: "Remember, you can't stare too long at a radiation cloud...") It seems to me that ", without fee, and without a written agreement" could be stripped out entirely. But I am not a lawyer. And while there is no problem asking the question here, if there is any actual money/liability on the line then relying on legal advice from geeks is about as sensible as asking your attorney for a custom kernel module. That goes for the whole stack of components in your system, not just PostgreSQL which is about the least likely to cause licensing problems. Cheers, Steve -- 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] Connection timeouts from pgAdmin
John, Just wanted to reply that this seems to have been the right track. Rather than change the firewall settings, our network administrator was able set postgres to send a keepalive to the client. Thanks, --Lee On Thu, Mar 4, 2010 at 5:26 PM, Lee Hachadoorian wrote: > On Thu, Mar 4, 2010 at 5:01 PM, John R Pierce wrote: >> are you running pgadmin and postgres server on the same computer, or on >> different computers? > > Different computers. >> >> if different computers, is there any sort of connection tracking in between, >> such as a NAT router/gateway? >> >> 15-20 mins sounds a lot like the typical NAT idle connection timeout... > > I will have to ask the network administrator and respond. > > Thanks, > --Lee -- 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] log_statement and syslog severity
On Wed, Mar 10, 2010 at 10:55 AM, Ben Chobot wrote: > On Mar 10, 2010, at 12:15 AM, Stuart Bishop wrote: > > > syslog doesn't give you easily machine readable output. I'm not sure how > syslog implementations handle high load (our sysadmins won't use it, so I > haven't investigated this further). > > Have you looked into syslog-ng? I believe it does just this sort of thing > for you. > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > We use syslog-ng for sorting our syslogs into different log files. It works well enough. What annoys me is that postgresql will split a SQL statement across several syslog "events". I know that syslog itself has a maximum message size and that this is required for really long SQL. However, I wanted to log each SQL statement as a single "event". Syslog-ng can direct output to a pipe. Putting these together I had begun working on a system where out syslog host (which logs this from lots of other, unrelated systems) would sort the postgresql logs to STDIN on a perl program that would reassemble the individual SQL statements and write them out in a different format. I never completed the project (just got busy with more important things). However, I thought that this approach was feasible. That being said, I would love it if Postgresql had logging mechanism plugin. Maybe it could load a so/dll that would handle logging. That so/dll would export a function like this: voidpg_log_init (/* some params related to the database instance */); voidpg_log_done (void); // called on database shutdown. voidpg_log_sql (int runtime, int rows, int status, const char *sql, const char *user); "status" would be some code to indicate if the sql was successful or not. The above is just off the top of my head. I've done no real research on if the above would be sufficient or correct. Just an idea...
[GENERAL] crosstab functionality for postgres 8.1.4
Hi, My postgres 8.1.4 installation does not have the crosstab functions. Most likely due to unavailability of contrib/tablefunc module. Is there a std version of tablefunc .so that can be downloaded and used ? thanks - Amol -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Naming conventions for lots of stored procedures
Hi all; One of my applications currently has over 60 stored procedures and future versions will likely have several hundred. I am wondering what folks find to be helpful naming conventions for managing a large number of stored procedures. We tried using double underscores to separate module vs procedure names and that just became a mess. I have found a few possible separators that might possibly work but they are aesthetically revolting (_$ for example, like select test_$echo(1);). I can't imagine I am the first person to run up against this problem and would rather ask advice of more experienced folks then to wander from one maintenance headache into a possibly far worse one. So, what are approaches each of you have taken in the past? Best Wishes, Chris Travers -- 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] log_statement and syslog severity
Stuart Bishop wrote: -- Start of PGP signed section. > > > On Wed, Mar 10, 2010 at 8:51 AM, Bruce Momjian wrote: > > Greg Sabino Mullane wrote: > >> Bruce replied: > >> ... > >> >> This means that, even using syslog as a destination, it's not possible > >> >> for > >> >> me to filter statements without some sort of log-text parsing, which I'd > >> >> prefer to avoid on effort, performance and data-integrity grounds. > >> > >> > Our logging system is very flexible, but not work-free on the user end. > >> > I don't see us changing things in that area. > >> > >> Bruce, that's a little harsh, I think the original poster has a legitimate > >> request. Personally, I'd love to be able to split the logs on various > >> things, > >> the most important to me being durations and per-database. I looked at the > >> code about a year ago to see how hard this would be and found it > >> non-trivial > >> (for me), as we're really assuming hard things go to a single filehandle. > >> It's definitely an area for improvement, and should be a TODO if not > >> already. > > > > This issue has been discussed and I think the community conclusion was > > that this should not be done by the database but rather by external > > tools. ?I think I was giving an accurate portrayal of the odds of this > > getting added. ?I do not think there is enough support for this to be a > > TODO item. > > How do you plug in this external tool? I think the real problem with log splitting in the server is designing a clean API to do this, and I haven't seen one yet. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Finding duplicates only.
Hi, Can someone please help me with this duplicate query. I'm trying to: 1. Return duplicates only. (without including the first valid record), and 2. Return as duplicate if the difference between a.inspection_time and b.inspection time is under 5 minutes. Here's the query string I'm using to retrieve the duplicates but it is returning every duplicate records. select a.rego, a.inspection_date, a.inspection_time, count(*) as c from inspections a where exists ( select null from inspections b where a.rego = b.rego and a.inspection_date = b.inspection_date and a.inspection_time = b.inspection_time group by b.rego, b.inspection_date, b.inspection_time ) and status_id in (0) group by a.rego, a.inspection_date, a.inspection_time having count(*) > 1 Thanks in advance. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] dst question
Hi, Australia will come out of DST on the 4th April 2010 at 03:00:00 and will be +9:30 from utc, currently we are +10:30 utc. I have some plpgsql functions which have variables of type timestamp defined with time zone. These variables are used in various ways, sometimes they will receive a text representation of a timestamp and are consequently populated with var := to_timestamp( text, 'format'), other times they are populated with existing timestamps from other tables columns or from the result of now(). The newly populated timestamp variable is then used to populate another tables 'timestamp with time zone' column. I have noticed that when we are within the hour as to when DST will reset ( ie 4th April 2010 between 02:00:00 and 03:00:00 ) the resulting timestamp put into our final table is already set to +9:30 utc even though we have not reached the actual time when dst changes. This happens when a string is converted using to_timestamp. Below is an example showing the results of a now() and to_timestamp( to_char(now(),'-mm-dd hh24:mi:ss'),'-mm-dd hh24:mi:ss') prior to entering the hour before the dst switch and within the hour of the dst switch. This is prior to entering the hour before dst changeover. both timestamps correctly show the +10:30 offset. # select now(), to_timestamp( to_char(now(),'-mm-dd hh24:mi:ss'),'-mm-dd hh24:mi:ss'); now| to_timestamp --+--- 2010-04-04 01:53:32.471086+10:30 | 2010-04-04 01:53:32+10:30 (1 row) This is 4 seconds into the last hour prior to dst changeover, now the to_timestamp result is showing +09:30 as the offset and now() correctly shows +10:30 # select now(), to_timestamp( to_char(now(),'-mm-dd hh24:mi:ss'),'-mm-dd hh24:mi:ss'); now| to_timestamp --+--- 2010-04-04 02:00:04.841797+10:30 | 2010-04-04 02:00:04+09:30 (1 row) Can someone explain as to why the output from the to_timestamp shows the offset at +09:30 when within the hour of the dst changeover and is this expected cheers. -- James. __ Information from ESET NOD32 Antivirus, version of virus signature database 4933 (20100310) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.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] crosstab functionality for postgres 8.1.4
On Wed, Mar 10, 2010 at 7:00 PM, Amol Chiplunkar wrote: > Hi, > > My postgres 8.1.4 installation does not have the crosstab > functions. Most likely due to unavailability of contrib/tablefunc > module. does too! http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/contrib/tablefunc/?only_with_tag=REL8_1_4 (upgrade to 8.1.19 ASAP!) merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Naming conventions for lots of stored procedures
On 3/10/2010 8:16 PM, Chris Travers wrote: > Hi all; > > One of my applications currently has over 60 stored procedures and > future versions will likely have several hundred. I am wondering what > folks find to be helpful naming conventions for managing a large > number of stored procedures. We tried using double underscores to > separate module vs procedure names and that just became a mess. I > have found a few possible separators that might possibly work but they > are aesthetically revolting (_$ for example, like select > test_$echo(1);). > > I can't imagine I am the first person to run up against this problem > and would rather ask advice of more experienced folks then to wander > from one maintenance headache into a possibly far worse one. > > So, what are approaches each of you have taken in the past? > > Best Wishes, > Chris Traverl > look into schemas. this allow group table and procedure logically and can limit access based on schemas. what i did is group procedures, views, and tables into schemas to keep them logically grouped. in one project there is 300 tables, and 1200 procedures wip (work in process) sales AR AP GL public All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored. CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately. Thank you. -- 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] SAS Raid10 vs SATA II Raid10 - many small reads and writes
On Wednesday 10 March 2010 18:32:41 Scott Marlowe wrote: > On Tue, Mar 9, 2010 at 11:49 PM, Phillip Berry > > wrote: > > Hi Everyone, > > > > We're in the market for a new DB server to replace our current one (yes > > it's one of *those* questions) ;). > > > > It'll have quad core Xeons, 36GB RAM and some sort of Raid 10 > > configuration. > > > > Our provider is pushing us towards 6 x SATA II disks in a Raid 10 > > configuration or 4 x SAS disks in Raid 10 (budget constraints). > > Are those your only two options? No 6 SAS drives? Are you looking at > 7200rpm or 10krpm SATA? 15krpm or 10krpm SAS? What RAID controller? > Battery backed Cache? Software RAID? > They're charging a lot more for SAS than for SATA so it's a budget constraint. And we can only fit six drives in the machine so that limits the number of drives for the SATA option. Hardware raid controller for both options, but I'm not sure what brand (yet). 15,000rpm SAS 10,000rpm SATA As with everything it's a trade off: 4 SAS drives or 6 SATA II drives in raid 10. I'm trying to find out if (for many many small reads and writes) one is more desirable than the other. Regards Phillip Berry -- 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] crosstab functionality for postgres 8.1.4
Merlin Moncure writes: > On Wed, Mar 10, 2010 at 7:00 PM, Amol Chiplunkar > wrote: >> My postgres 8.1.4 installation does not have the crosstab >> functions. Most likely due to unavailability of contrib/tablefunc >> module. > does too! I think the problem is the OP didn't install postgresql-contrib package, or possibly didn't run the tablefunc.sql script to make the functions available in his database. 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] Naming conventions for lots of stored procedures
On Wed, Mar 10, 2010 at 7:08 PM, Justin Graf wrote: > look into schemas. > > this allow group table and procedure logically and can limit access > based on schemas. > > what i did is group procedures, views, and tables into schemas to keep > them logically grouped. > in one project there is 300 tables, and 1200 procedures > wip (work in process) > sales > AR > AP > GL > public There are two major limitations here of schemas: 1) They can't be nested leading again to possible namespace ambiguity. 2) there are a number of requests to try to get the application to install into an arbitrary, nonpublic schema. If schemas could be nested this would solve both of these problems. However, if the above is anywhere near a complete list of schemas for 1200 procedures, you must also have some strong naming conventions to prevent collisions. I would be interested in what they are. Best wishes, Chris Travers -- 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] dst question
jgirvin writes: > Australia will come out of DST on the 4th April 2010 at 03:00:00 and > will be +9:30 from utc, currently we are +10:30 utc. OK, so this is a "fall back" transition for you guys, right? > This is 4 seconds into the last hour prior to dst changeover, now the > to_timestamp result is showing +09:30 as the offset and now() correctly > shows +10:30 > # select now(), to_timestamp( to_char(now(),'-mm-dd > hh24:mi:ss'),'-mm-dd hh24:mi:ss'); > now| to_timestamp > --+--- > 2010-04-04 02:00:04.841797+10:30 | 2010-04-04 02:00:04+09:30 > (1 row) > Can someone explain as to why the output from the to_timestamp shows the > offset at +09:30 when within the hour of the dst changeover and is this > expected The problem is that the output of to_char() is ambiguous, since you didn't include the timezone in the format spec. Times between 02:00 and 03:00 occur twice on that date, and there's no way to know which time "02:00:04" refers to. The assumption that to_timestamp uses (along with our other datetime input code) is that an ambiguous time should be resolved as standard time, ie, the second occurrence of "02:00:04". 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] Finding duplicates only.
In response to Greenhorn : > Hi, > > Can someone please help me with this duplicate query. > > I'm trying to: > > 1. Return duplicates only. (without including the first valid record), and I will try to help you. Assuming this table: test=*# select * from greenhorn order by id; id | inspection_time + 1 | 2010-03-11 07:14:14.290259 1 | 2010-03-11 07:14:14.290259 2 | 2010-03-11 07:14:14.290259 3 | 2010-03-11 07:15:14.290259 4 | 2010-03-11 07:16:14.290259 5 | 2010-03-11 07:24:14.290259 6 | 2010-03-11 07:34:14.290259 (7 rows) The record with id=1 is twice. > 2. Return as duplicate if the difference between a.inspection_time > and b.inspection time is under 5 minutes. Assuming you have a 8.4-version: with the table above, and time-difference < 2 minutes, rows 2, 3 and 4: test=*# select * from ( select id, inspection_time, lag(inspection_time) over (order by inspection_time RANGE UNBOUNDED PRECEDING) from greenhorn group by 1,2 ) foo where inspection_time-lag < '2minutes'::interval; id | inspection_time |lag ++ 2 | 2010-03-11 07:14:14.290259 | 2010-03-11 07:14:14.290259 3 | 2010-03-11 07:15:14.290259 | 2010-03-11 07:14:14.290259 4 | 2010-03-11 07:16:14.290259 | 2010-03-11 07:15:14.290259 (3 rows) > > Here's the query string I'm using to retrieve the duplicates but it is > returning every duplicate records. > > select a.rego, > a.inspection_date, > a.inspection_time, Why do you have 2 fields, one for date and one for time? Use one timestamp-field instead. Regards, hope that helps, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Statement Triggers
In response to Gordan Bobic : > Specifically, what features of the SQL statement that triggered the event > are available to the function invoked by the trigger? Say I wanted to write http://www.postgresql.org/docs/8.4/interactive/plpgsql-trigger.html Except for NEW and OLD. > all INSERT statements executed on a table into a log file. How would I > access the original statement in the triggered function? If I cannot access You have no access, the statement is not visible. You can use such a TRIGGER, for instance, to send a message to clients via LISTEN/NOTIFY. Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] SAS Raid10 vs SATA II Raid10 - many small reads and writes
On Thursday, March 11, 2010, Phillip Berry wrote: > On Wednesday 10 March 2010 18:32:41 Scott Marlowe wrote: >> On Tue, Mar 9, 2010 at 11:49 PM, Phillip Berry >> >> wrote: >> > Hi Everyone, >> > >> > We're in the market for a new DB server to replace our current one (yes >> > it's one of *those* questions) ;). >> > >> > It'll have quad core Xeons, 36GB RAM and some sort of Raid 10 >> > configuration. >> > >> > Our provider is pushing us towards 6 x SATA II disks in a Raid 10 >> > configuration or 4 x SAS disks in Raid 10 (budget constraints). >> >> Are those your only two options? No 6 SAS drives? Are you looking at >> 7200rpm or 10krpm SATA? 15krpm or 10krpm SAS? What RAID controller? >> Battery backed Cache? Software RAID? >> > > They're charging a lot more for SAS than for SATA so it's a budget > constraint. And we can only fit > six drives in the machine so that limits the number of drives for the SATA > option. > > Hardware raid controller for both options, but I'm not sure what brand (yet). > > 15,000rpm SAS > 10,000rpm SATA > > > As with everything it's a trade off: 4 SAS drives or 6 SATA II drives in raid > 10. I'm trying to find > out if (for many many small reads and writes) one is more desirable than the > other. How much do you have to cut your CPU and/or memory (CPU first!) to get to 6 SAS? It may be well worth considering -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
