Re: [GENERAL] Free OLAP software for Postgres databas
The first is easy; simply configure data sources pointed at the PostgreSQL database. Note that the JDBC drivers that ship with Pentaho are, in my experience, of inconsistent version, and you might want to update them to the latest available for your PostgreSQL and Java versions. In my experience you don't want to upgrade the jars that come with Pentaho. If it is not the versions that they come with the chances are that things will not work. The JDBC driver might be upgradeable, I believe we downloaded that by ourselves. Also we had a much better experience with Jasper (free version) then Pentaho (free version). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Unexpected behaviour of date_part
This is PostgreSQL 8.4, but the behaviour has not changed from earlier versions:
test= SHOW timezone;
TimeZone
---
Europe/Vienna
(1 row)
test= SELECT date_part('timezone_hours', timestamp with time zone '2009-06-26
10:05:57.46624+11');
date_part
---
2
(1 row)
2 being the offset of my local time zone.
Now an EXPLAIN shows that this is due to the fact that the timestamp
is converted to my local time zone before it is submitted to the function,
but I think that this result is undesirable and misleading.
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
Re: [GENERAL] masking the code
On 2009-06-26, luca.cicirie...@email.it luca.cicirie...@email.it wrote: I've wrote a PLPGSQL stored procedure for a DB I've to delivery to my customer. The problem is that I want to hide the code of the stored procedure. I don't want that my customer is able to read the code of the my sp. Do exist a way to mask the code of the store procedure shipped with my DB? rewrite it in a compiled language. -- 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] pasting into psql garbles text
On 2009-06-27, Merlin Moncure mmonc...@gmail.com wrote: I've noticed over a wide variety of operating systems that when you paste from an application into psql through a terminal (currently using the default gnome terminal in ubuntu), large pastes tend to get garbled with some of the input getting truncated. While working on functions, this is annoying in the extreme. Interestingly, the one platform that tends not to suffer from this is windows so I'm guessing this is a readline problem. Has anybody else noticed this? Is there a workaround? one way to disable readline is to use cat|psql instead of psql -- 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] pasting into psql garbles text
In my case (FreeBSD 6 and 7) psql does not freezes, but for some reason part of pasted code get corrupted when size of the code relatively big. It seems like some timeout used, or buffer overflows: 1. locally with xterm for the first time pasting function of 9K: base= create or replace function football_recalc_match_stat(bigint) returns void as $$ declare here i see rows 1-52 of function without 'base$' prefix where football_match = match and match_event_type in (3, 12)base$ declare base$ match alias for $1; here i see rows 1-117 of function with 'base$' prefix here i see rows 166-till the end of the func with 'base$' prefix and got error in function body at the row 118. 2-10 locally and via ssh to localhost with xterm -- no problem. 11 - to remote host via ssh with xterm base= create or replace function football_recalc_match_stat(bigint) returns void as $$ declare here i see rows 1-52 of function without 'base$' prefix where football_match = match and match_event_type in (3, 12)sovsport$ declare base$ match alias for $1; here i see rows 1-23 of function with 'base$' prefix, last row truncated base$ and that's all 11 stable reproduced for several times with problems on the same rows. uname -a FreeBSD 6.3-RELEASE-p2 FreeBSD 6.3-RELEASE-p2 #0: Wed Sep 3 09:41:48 MSD 2008 i386 set | grep LANG LANG=ru_RU.UTF-8 pkg_info -r postgresql-client-8.2.7 Information for postgresql-client-8.2.7: Depends on: Dependency: libiconv-1.11_1 Dependency: gettext-0.16.1_3 Tom Lane wrote: Merlin Moncure mmonc...@gmail.com writes: I'm starting to feel like my problems start appearing at a very fixed size (like you, a few hundred or so). Do you see this in other programs (bash, vim, etc)? or only psql? I've only noticed it in psql, but there are not that many other programs that I tend to paste lots of input into. (experiments...) Hmm, and another interesting thing is that it only seems to happen on my HPUX system, which is (intentionally) running a pretty ancient version of readline ... 4.2a looks like. My Fedora 10 box with readline 5.2 eats the same amount of pasted text without indigestion. What readline version are you using? regards, tom lane
Re: [GENERAL] possible bug on age() function (8.2.4 , 8.3.6)
On 2009-06-24, Philippe Amelant pamel...@companeo.com wrote:
Le mercredi 24 juin 2009 à 12:45 +, Jasen Betts a écrit :
On 2009-06-24, Philippe Amelant pamel...@companeo.com wrote:
Ok but if I work with hours or whatever the problem is still there
SELECT (EXTRACT(EPOCH FROM TIMESTAMP '2009-06-23 18:36:05.064066+02') -
EXTRACT(EPOCH FROM TIMESTAMP '2009-05-12 18:36:05.064066+02'))/3600,
EXTRACT(EPOCH FROM interval '1008 hours')/3600, age('2009-06-23
18:36:05.064066+02' ,'2009-05-12 18:36:05.064066+02') interval '1007
hours';
The third test should be true and not false
The third test is comparing a double with an interval.
compare like with like.
SELECT (EXTRACT(EPOCH FROM TIMESTAMP '2009-06-23 18:36:05.064066+02') -
EXTRACT(EPOCH FROM TIMESTAMP '2009-05-12 18:36:05.064066+02'))/3600,
EXTRACT(EPOCH FROM interval '1008 hours')/3600, age('2009-06-23
18:36:05.064066+02' ,'2009-05-12 18:36:05.064066+02')
EXTRACT(EPOCH FROM interval '1007 hours');
From de doc : age(timestamp, timestamp) return an interval so if I
wrote
select age('2009-06-23 18:36:05.064066+02' ,'2009-05-12 18:36:05.064066
+02') interval '1000 hours';
I think I compare an interval with an interval.
that does but they are different units. one in months and days and the other in
hours.
if you want to count days subtract dates,
the result should be true because there is 1008 hours between the 2
dates
but there is not reliably 1008 hours in 1 mon 11 days
if the context of the interval is important apply it,
select timestamptz '2009-05-12 18:36:05.064066+02'
,timestamptz '2009-06-23 18:36:05.064066+02'
,interval '1007 hours'
,timestamptz '2009-05-12 18:36:05.064066+02' + interval '1007 hours'
,timestamptz '2009-05-12 18:36:05.064066+02' + interval '1007 hours'
timestamptz '2009-06-23 18:36:05.064066+02';
But I need to substract more than 24 hours to get a 'true'
select age('2009-06-23 18:36:05.064066+02' ,'2009-05-12 18:36:05.064066
+02') interval '983 hours';
age() works but is not well suited to that use:
select timestamptz '2009-05-12 18:36:05.064066 +02' + age('2009-06-23
18:36:05.064066+02' ,'2009-05-12 18:36:05.064066 +02');
--
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] Unexpected behaviour of date_part
Albe Laurenz wrote:
test= SELECT date_part('timezone_hours', timestamp with time zone '2009-06-26
10:05:57.46624+11');
date_part
---
2
(1 row)
2 being the offset of my local time zone.
Now an EXPLAIN shows that this is due to the fact that the timestamp
is converted to my local time zone before it is submitted to the function,
but I think that this result is undesirable and misleading.
Basically, timestamp with time zone is a bad name for the type. If it
was called absolute time the behaviour would make sense. The query
below returns true, which makes sense if they are absolute times.
SELECT '29/06/2009 10:54:55+01'::timestamptz =
'29/06/2009 11:54:55+02'::timestamptz;
What would be useful sometimes is a type timestamp AND time zone which
stored each separately and where the above wouldn't be true. I think
it's been discussed, but no-one has done the necessary work on it.
--
Richard Huxton
Archonet Ltd
--
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] Replication and coding good practices
On Sun, 2009-06-28 at 09:01 -0700, David Fetter wrote: Are there any rules of thumb to consider for making an application easier to work with a general replication solution? The applications I mostly deal with are e-commerce sites. It really depends on what replication solution you choose, along with the environment you're deploying into. ... and why you need replication. Reliability/Availability? Data storage redundancy? Performance? And if performance, read-mostly performance or write-heavy performance? That said, I've noticed that the things that are generally good practice help you even more when you're doing replication. Practices I've seen help directly: * Separate read users and code from write users and code. * Separate DDL from both of the above. * Make DDL changes part of your deployment process and only allow them in files which track in your SCM system. Version your schema, storing the schema version in a 1-row table or even as a stable function. This makes it much easier for deployment tools or staff to easily see what needs to be done to get the schema and app to the latest version - there's no what the hell is the current state of this thing, anyway? to worry about. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Python client + select = locked resources???
Hi!
I wanna ask something. I came from IB/FB world.
In this world I was everytime in transaction, because of reads are also
working under transactions.
In the FB world the transactions without any writes/updates are not
locking the database, so another clients can makes a transactions on any
records.
And also can add new fields to the tables.
Now I used Pylons webserver (Python) with PyGRESQL, and DBUtils for
cached database connections/cursors.
Today I saw a locking situation in many times.
0.) I started Pylons web server, and in the browser I request for a
simple view (without modify anything).
1.) I opened PGAdmin.
2.) I move the focus to the table X.
3.) I opened an SQL editor and try to make two column adds:
alter table X add test_a date;
alter table X add test_b date;
4.) After the the PGAdmin's Query Execution (F5) nothing happened. I see
this menu is disabled, and PGAdmin is locked for new operations.
5.) When I simply close Pylons web server, the PGAdmin quickly
finished with this table restructure without problems...
The problem can repeatable in any times.
This is very hateable thing, because in this view I don't modify
anything, I use only selects, nothing other things.
And I wanna solve this problem, because if I must do some modifications
in the online database (for example: add a new field), I don't want to
shut down the webserver with all online clients...
I simplified this bug to see this without web server, dbutils, and
other layers.
I wrote this python code:
import os, sys, pgdb
fmtstring = '%s:%s:%s:%s'
fmtstring = fmtstring % ('127.0.0.1',
'anydb',
'anyuser', 'what?')
db = pgdb.connect (fmtstring)
print ok
cur = db.cursor()
cur.execute('select * from testtable')
rek = cur.fetchone()
cur.close()
while 1:
pass
db.close()
After start this I tried to add a new field to the testtable from
PGAdmin's Query GUI:
alter table testtable add test_001 date;
With the cur.execute(select * from testtable) I got lock error, the
PGAdmin query is running and running and running... :-(
Without this cur.execute the alter table finished without locks.
When I put a db.rollback() before while the lock vanished...
So pg is hold all records I fetched? They are not useable freely in a
simple, readonly select?
Please help me SOS if possible, I must find a way to get out from these
locks!
And please help me: how to check that I'm in transaction or not?
Thanks for your help:
dd
[GENERAL] Slony-I timezone setting
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi All,
When configuring a Slony cluster I get the infamous 'ERROR: invalid
input syntax for type timestamp: Mon Jun 29 13:00:36.628805 2009 WEST'
I know that this is a timezone setting issue.
In my case I have my system set to 'Atlantic/Madeira' and UTC.
My postgresql.conf has the same setting ('Atlantic/Madeira')
Postgresql ver. 8.3.7 on Fedora 8
I feel reluctant to follow Slony's 'Best practices' depicted in the
(cough) fine manual as it would mean losing any DST awareness ...
Any thoughts appreciated,
TIA,
- --
Pedro Doria Meunier
GSM: +351 96 17 20 188
Skype: pdoriam
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.7 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org
iD8DBQFKSLaI2FH5GXCfxAsRAkPpAJ48qThWwTWwwIRK802T/Tyn9ztyvgCfWjw3
kkUnMNb1hmKNYZ5dmM04C7U=
=3XOr
-END PGP SIGNATURE-
--
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] Replication and coding good practices
On Mon, 29 Jun 2009 19:11:43 +0800 Craig Ringer cr...@postnewspapers.com.au wrote: On Sun, 2009-06-28 at 09:01 -0700, David Fetter wrote: Are there any rules of thumb to consider for making an application easier to work with a general replication solution? The applications I mostly deal with are e-commerce sites. It really depends on what replication solution you choose, along with the environment you're deploying into. ... and why you need replication. Reliability/Availability? Data storage redundancy? Performance? And if performance, read-mostly performance or write-heavy performance? 1) performance, read-mostly 2) reliability I'm making large use of plpgsql mainly for: - encapsulation - single point of truth - implicit transaction Most of the write operations don't have to be aware of a multi user environment. Still in some part of the code things have to be aware of transactions, multi user environment (or better multiple connections from the same user) etc... Not only these parts are rare, they are seldom executed too. So my main concern about the parts that may be problematic in a replicated context is to keep maintenance low and development easy. eg. I've started to use temp tables but I guess they may cause some problems in conjunction with connection pooling systems. That said, I've noticed that the things that are generally good practice help you even more when you're doing replication. Practices I've seen help directly: * Separate read users and code from write users and code. * Separate DDL from both of the above. * Make DDL changes part of your deployment process and only allow them in files which track in your SCM system. Version your schema, storing the schema version in a 1-row table or even as a stable function. This makes it much easier for deployment tools or staff to easily see what needs to be done to get the schema and app to the latest version - there's no what the hell is the current state of this thing, anyway? to worry about. This is another area I'd like to learn more about available techniques for managing development. But currently I was more interested in coding techniques to avoid maintenance/porting problems once I'll have to support a replication solution. At the moment schema changes are saved in a file together with the web application code. I was thinking to automate the application of schema changes with a hook in svn, but right now it doesn't look as a good investment. Still I'm very interested in techniques to version schema changes and bring them together with code change and being able to diff them. thanks -- 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
[GENERAL] Create db with template does not transfer ownership
I'm trying to create a PostGIS database by using CREATE DATABASE mydb TEMPLATE template_postgis; using the Windows one-click installer package, with PostGIS 1.3 / Postgres 8.3.7. But my question is more general: When a new database is created base on a template, the owner of those tables is not changed. The owner of the copied tables remains the original owner in the template. This leaves the owner of the new DB unable to access those tables. The end result is that you cannot use a system-wide template database, unless you want to grant full access to these tables. Should the action of the 'TEMPLATE' option not be to transfer ownership to the owner of the new DB? I don't understand how one is supposed to use this mechanism. Thanks, Ben -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Am I in intransaction or in autocommit mode?
Hi! Can I check with something that I'm in in-transaction or in autocommit mode? I wanna avoid the notices I got when I'm also in mode I need... For example: begin begin --- error notice... Thanks for your help: dd ps: in my prev. mail I asked this too, but in another context... possible this subject is better for this question. -- 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] masking the code
On Fri, Jun 26, 2009 at 3:37 PM, arta...@comcast.net wrote: I completely agree w/ HArald. Its not something we'd want to see in an open source product. That said, I saw yesterday that the latest version of EnterpriseDB has this feature. So if you want to protect your own IP, then you've got to purchase someone else's. Release 2 of our proprietary Advanced Server 8.3 does include the 'edbwrap' functionality. It was included based on demand from ISV's who are used to wrapping their code when distributing an app. It is important to note (as many people have already pointed out) that both EnterpriseDB and Oracle's wrap functionality is declared as a 100% guarantee that nobody can read your code. As with many different types of security (i.e. the 3 foot high fence) this is really just a deterrent to most people who either aren't capable of reverse engineering or are just not interested in the first place. http://www.enterprisedb.com/docs/en/8.3R2/oracompat/EnterpriseDB_OraCompat_8.3-211.htm#P15495_739546 http://www.databasejournal.com/features/oracle/article.php/3382331/Oracles-Wrap-Utility.htm http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/c_wrap.htm --Scott
Re: [GENERAL] Am I in intransaction or in autocommit mode?
In response to durumdara : Hi! Can I check with something that I'm in in-transaction or in autocommit mode? I wanna avoid the notices I got when I'm also in mode I need... For example: begin begin --- error notice... Warning, not error. In psql, you can set the PROMPT: \set PROMPT1 '%/%R%x%# ' test=# begin; BEGIN test=*# Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- 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] masking the code
On Mon, Jun 29, 2009 at 9:31 AM, Scott Mead scott.li...@enterprisedb.comwrote: It is important to note (as many people have already pointed out) that both EnterpriseDB and Oracle's wrap functionality is declared as a 100% guarantee that nobody can read your code. As with many different types of security (i.e. the 3 foot high fence) this is really just a deterrent to most people who either aren't capable of reverse engineering or are just not interested in the first place. s/is declared/is NOT declared/g :) -- Jonah H. Harris, Senior DBA myYearbook.com
Re: [GENERAL] masking the code
On Mon, Jun 29, 2009 at 9:35 AM, Jonah H. Harris jonah.har...@gmail.comwrote: On Mon, Jun 29, 2009 at 9:31 AM, Scott Mead scott.li...@enterprisedb.comwrote: It is important to note (as many people have already pointed out) that both EnterpriseDB and Oracle's wrap functionality is declared as a 100% guarantee that nobody can read your code. As with many different types of security (i.e. the 3 foot high fence) this is really just a deterrent to most people who either aren't capable of reverse engineering or are just not interested in the first place. s/is declared/is NOT declared/g Yes! Jeez, this cold is getting to me. Thanks Jonah... : It is important to note (as many people have already pointed out) that both EnterpriseDB and Oracle's wrap functionality is NOT declared as a 100% guarantee that nobody can read your code. --Scott
Re: [GENERAL] another can't connect
BJ Freeman bjf...@free-man.net writes: sorry about the post did not do a reply all and sent a personal replay yes in the chain I have ACCEPT all -- anywhere anywherestate RELATED,ESTABLISHED it is the next to last rule. You sure that works? This notation for iptables isn't familiar to me, but I'd have thought you have to specify the state module. The comparable line in my iptables looks like -A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT Come to think of it, the state NEW test in your other line would have to addressed to the state module as well. BTW, usual practice is to put the established-connections rule near the start of the chain, not the end, on the grounds that the majority of packets the kernel will see will match this rule and so you want to test it sooner rather than later. 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] Slony-I timezone setting
Pedro Doria Meunier pdo...@netmadeira.com writes: When configuring a Slony cluster I get the infamous 'ERROR: invalid input syntax for type timestamp: Mon Jun 29 13:00:36.628805 2009 WEST' You need to make the timezone_abbreviations configuration on the slave match that on the master. Alternatively, set datestyle to ISO so that a less ambiguous timestamp format is used. 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] Free OLAP software for Postgres databas
On Mon, Jun 29, 2009 at 09:02:30AM +0300, Sim Zacks wrote: The first is easy; simply configure data sources pointed at the PostgreSQL database. Note that the JDBC drivers that ship with Pentaho are, in my experience, of inconsistent version, and you might want to update them to the latest available for your PostgreSQL and Java versions. In my experience you don't want to upgrade the jars that come with Pentaho. If it is not the versions that they come with the chances are that things will not work. The JDBC driver might be upgradeable, I believe we downloaded that by ourselves. We've not had problems upgrading the JDBC drivers (or indeed installing them, as IIRC, some components just haven't had a PostgreSQL driver in the default installation). Your Mileage May Vary. We haven't touched any other libraries that ship with Pentaho, which I tend to think is a wise decision on our part :) Also we had a much better experience with Jasper (free version) then Pentaho (free version). When we tried to decide between the two, we took both out for brief test drives. They seemed almost functionally equivalent, and there were no major advantages or deficiencies we found to indicate one over the other. But that's a decision made with regard only to those features we cared about. - Josh / eggyknap signature.asc Description: Digital signature
Re: [GENERAL] Replication and coding good practices
On Mon, Jun 29, 2009 at 07:11:43PM +0800, Craig Ringer wrote: On Sun, 2009-06-28 at 09:01 -0700, David Fetter wrote: Are there any rules of thumb to consider for making an application easier to work with a general replication solution? The applications I mostly deal with are e-commerce sites. It really depends on what replication solution you choose, along with the environment you're deploying into. ... and why you need replication. Reliability/Availability? Data storage redundancy? Performance? And if performance, read-mostly performance or write-heavy performance? It's this kind of discussion that you might want to hire experts to help with :) Commandprompt, Endpoint, OmniTI and the outfit I work for, PostgreSQL Experts http://www.pgexperts.com would be examples. That said, I've noticed that the things that are generally good practice help you even more when you're doing replication. Practices I've seen help directly: * Separate read users and code from write users and code. * Separate DDL from both of the above. * Make DDL changes part of your deployment process and only allow them in files which track in your SCM system. Version your schema, storing the schema version in a 1-row table or even as a stable function. This makes it much easier for deployment tools or staff to easily see what needs to be done to get the schema and app to the latest version - there's no what the hell is the current state of this thing, anyway? to worry about. When versioning schemas, make sure your deployment tools are always atomic and that the schema version can't be modified by anything but those tools. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Slony-I timezone setting
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Thanks Tom for your thoughts :) I tried what you suggested to no avail :-( Looking at this more closely I see this format when the error arises: Mon Jun 29 15:28:10.952151 2009 WEST Curiously enough this is what the following command throws out of /etc/localtime: zdump -v /etc/localtime | grep 2009 /etc/localtime Sun Mar 29 00:59:59 2009 UTC = Sun Mar 29 00:59:59 2009 WET isdst=0 gmtoff=0 /etc/localtime Sun Mar 29 01:00:00 2009 UTC = Sun Mar 29 02:00:00 2009 WEST isdst=1 gmtoff=3600 /etc/localtime Sun Oct 25 00:59:59 2009 UTC = Sun Oct 25 01:59:59 2009 WEST isdst=1 gmtoff=3600 /etc/localtime Sun Oct 25 01:00:00 2009 UTC = Sun Oct 25 01:00:00 2009 WET isdst=0 gmtoff=0 Even though I'm *sure* that the system's timezone is set to 'Atlantic/Madeira'... This is what's defined in postgresql.conf datestyle = 'iso, ymd' timezone = 'Atlantic/Madeira' BR, Pedro Doria Meunier GSM: +351 96 17 20 188 Skype: pdoriam Tom Lane wrote: Pedro Doria Meunier pdo...@netmadeira.com writes: When configuring a Slony cluster I get the infamous 'ERROR: invalid input syntax for type timestamp: Mon Jun 29 13:00:36.628805 2009 WEST' You need to make the timezone_abbreviations configuration on the slave match that on the master. Alternatively, set datestyle to ISO so that a less ambiguous timestamp format is used. regards, tom lane -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFKSNJs2FH5GXCfxAsRAmCJAKC/WBdfkUmkKUgvBvrSwD0dLOoGmwCcCPb7 UJRvPCTdSKCUkiOoEXf/WmU= =QEO6 -END PGP SIGNATURE- -- 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] Switching from MySQL: ON DUPLICATE KEY UPDATE, plpgsql function
To migrate the site, you can use an open source ETL tool. Talend Open Studio is an open source ETL tool for data integration and migration experts. It's easy to learn for a non-technical user. What distinguishes Talend, when it comes to business users, is the tMap component. It allows the user to get a graphical and functional view of integration processes. For more information: http://www.talend.com/ Justin-95 wrote: APseudoUtopia wrote: thread, then logs out (intending to read all the other forum threads at some point in the future when they log in again). If I used a VIEW, it would automatically consider all those unread forum posts to be read when the user logs out. That wouldn't work. What if a user logs in, reads only one forum You are keeping a list of all the forums a user has read, i would not worry about making sure the table tracking user activity has duplicate key values. The select can be limited to return just on row with the highest time stamp then compare this result to figure out what forms the user has not read yet. This eliminates one of problems but creates a problem where table tracking user activity is going bloat but in low traffic times delete the duplicate values. A similar topic was discussed on the performance mailing list, where updates are hung for several seconds for a similar tracking table... http://archives.postgresql.org/pgsql-performance/2009-06/msg00300.php -- View this message in context: http://www.nabble.com/Switching-from-MySQL%3A-ON-DUPLICATE-KEY-UPDATE%2C-plpgsql-function-tp24237803p24254206.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] masking the code
On Mon, Jun 29, 2009 at 9:35 AM, Jonah H. Harris jonah.har...@gmail.comwrote: On Mon, Jun 29, 2009 at 9:31 AM, Scott Mead scott.li...@enterprisedb.comwrote: It is important to note (as many people have already pointed out) that both EnterpriseDB and Oracle's wrap functionality is declared as a 100% guarantee that nobody can read your code. As with many different types of security (i.e. the 3 foot high fence) this is really just a deterrent to most people who either aren't capable of reverse engineering or are just not interested in the first place. s/is declared/is NOT declared/g Yes! Jeez, this cold is getting to me. Thanks Jonah... : It is important to note (as many people have already pointed out) that both EnterpriseDB and Oracle's wrap functionality is NOT declared as a 100% guarantee that nobody can read your code. --Scott :) -- Jonah H. Harris, Senior DBA myYearbook.com
Re: [GENERAL] masking the code
Jasen Betts schrieb: I've wrote a PLPGSQL stored procedure for a DB I've to delivery to my customer. The problem is that I want to hide the code of the stored procedure. I don't want that my customer is able to read the code of the my sp. Do exist a way to mask the code of the store procedure shipped with my DB? rewrite it in a compiled language. And hope, that the customer could not read the result. Greetings, Torsten -- 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] pasting into psql garbles text
Le samedi 27 juin 2009, Merlin Moncure a écrit : I've noticed over a wide variety of operating systems that when you paste from an application into psql through a terminal (currently using the default gnome terminal in ubuntu), large pastes tend to get garbled with some of the input getting truncated. While working on functions, this is annoying in the extreme. I had notice the same error with konsole : http://bugs.kde.org/show_bug.cgi?id=150957 Interestingly, the one platform that tends not to suffer from this is windows so I'm guessing this is a readline problem. Has anybody else noticed this? Is there a workaround? merlin -- Cédric Villemain Administrateur de Base de Données Cel: +33 (0)6 74 15 56 53 http://dalibo.com - http://dalibo.org signature.asc Description: This is a digitally signed message part.
Re: [GENERAL] pasting into psql garbles text
On Sat, Jun 27, 2009 at 5:12 PM, Tom Lanet...@sss.pgh.pa.us wrote: Merlin Moncure mmonc...@gmail.com writes: I'm starting to feel like my problems start appearing at a very fixed size (like you, a few hundred or so). Do you see this in other programs (bash, vim, etc)? or only psql? I've only noticed it in psql, but there are not that many other programs that I tend to paste lots of input into. (experiments...) Hmm, and another interesting thing is that it only seems to happen on my HPUX system, which is (intentionally) running a pretty ancient version of readline ... 4.2a looks like. My Fedora 10 box with readline 5.2 eats the same amount of pasted text without indigestion. What readline version are you using? I'm currently using 'ubuntu intrepid', which has very modern everything (readline 5.2-3 build1). I've noticed this problem since the beginning of time now. This is definitely a 'psql + something' problem...I can paste into psql query buffer editor (vim) following a /r/e with no problems, but not directly into psql itself. Another interesting point: the problem manifests with pasts over a certain size, but tends to bork at a particular point. The size of the psql window affects this...smaller windows are more tolerant of larger (but still not very large) pastes. 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] [PERFORM] Terrible Write Performance of a Stored Procedure
On Fri, Jun 26, 2009 at 4:36 PM, Brian Troutwinegoofyheadedp...@gmail.com wrote: *) use indexes to optimize where and join conditions. for example, update yadda set yadda where foo = bar, make sure that there is an index on foo. As alan noted this is almost definitely your problem. To my knowledge, I have. amazon_items.isbn does not have an index but it is not used, unless I'm overlooking something, in a where condition. item_details.isbn is and does, however. *) use varchar, not char (always). Why? char(n) included the padding up to 'n' both on disk and in data returned. It's slower and can be wasteful. Did you figure out your issue? I'm pretty sure its an index issue or some other basic optimization problem. 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] Slony-I timezone setting
Pedro Doria Meunier pdo...@netmadeira.com writes: This is what's defined in postgresql.conf datestyle = 'iso, ymd' timezone = 'Atlantic/Madeira' Hmm. WET/WEST are the zone abbreviations for that zone, all right, but I don't understand why they're being emitted if you have that datestyle setting. Maybe something is overriding the datestyle for some dumb reason? Anyway, a look at the default timezone abbrevs file shows that it recognizes WETDST not WEST. You might care to add WEST as an accepted abbrev too. 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] Switching from MySQL: ON DUPLICATE KEY UPDATE, plpgsql function
--- On Mon, 6/29/09, Tguru g...@talend.com wrote: From: Tguru g...@talend.com Subject: Re: [GENERAL] Switching from MySQL: ON DUPLICATE KEY UPDATE, plpgsql function To: pgsql-general@postgresql.org Date: Monday, June 29, 2009, 1:33 PM To migrate the site, you can use an open source ETL tool. Talend Open Studio is an open source ETL tool for data integration and migration experts. It's easy to learn for a non-technical user. What distinguishes Talend, when it comes to business users, is the tMap component. It allows the user to get a graphical and functional view of integration processes. For more information: http://www.talend.com/ Justin-95 wrote: APseudoUtopia wrote: thread, then logs out (intending to read all the other forum threads at some point in the future when they log in again). If I used a VIEW, it would automatically consider all those unread forum posts to be read when the user logs out. That wouldn't work. What if a user logs in, reads only one forum You are keeping a list of all the forums a user has read, i would not worry about making sure the table tracking user activity has duplicate key values. The select can be limited to return just on row with the highest time stamp then compare this result to figure out what forms the user has not read yet. This eliminates one of problems but creates a problem where table tracking user activity is going bloat but in low traffic times delete the duplicate values. A similar topic was discussed on the performance mailing list, where updates are hung for several seconds for a similar tracking table... http://archives.postgresql.org/pgsql-performance/2009-06/msg00300.php another option is Pentaho, is good and easy too http://kettle.pentaho.org/ -- 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] Slony-I timezone setting
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom, This is what I have in '/usr/share/pgsql/timezonesets/Atlantic.txt': WEST 3600 D # Western Europe Summer Time # (Atlantic/Canary) # (Atlantic/Faeroe) # (Atlantic/Madeira) # (Europe/Lisbon) I copied this portion into '/usr/share/pgsql/timezonesets/Default' and restarted the service. Still no go :( I even tried alter user user-slony set timezone='WEST'; After the mods this what the query gives: ERROR: unrecognized time zone name: WEST And this is when I ran out of ideas... btw: do you happen to know of a Slony mailing list? TIA, Pedro Doria Meunier GSM: +351 96 17 20 188 Skype: pdoriam Tom Lane wrote: Pedro Doria Meunier pdo...@netmadeira.com writes: This is what's defined in postgresql.conf datestyle = 'iso, ymd' timezone = 'Atlantic/Madeira' Hmm. WET/WEST are the zone abbreviations for that zone, all right, but I don't understand why they're being emitted if you have that datestyle setting. Maybe something is overriding the datestyle for some dumb reason? Anyway, a look at the default timezone abbrevs file shows that it recognizes WETDST not WEST. You might care to add WEST as an accepted abbrev too. regards, tom lane -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFKSOBE2FH5GXCfxAsRAr+8AJsHvnlpWWZw7rVb2Kp9A70Q4/DJPwCfXbrb L+n1Km17aMA7AzhUe7IqvPs= =THOj -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] GIN and GiST index - more in depth info
Hi everybody, actually I am wondering if anybody can give me some links to a more in depth info concerning the GIN and GiST index. After having read the docu and searching the web for more info, I am still not satisfied with the knowledge I have. For sure I had a look to Oleg Bartunov' s and Teodor Sigaev's website at http://www.sai.msu.su/~megera/wiki/ but for me it's still not clear how to describe the differences between the indexes and the usage scenarios when to use GIN or GiST. Every info is higly appreciated. Thanks Andy -- Netzmeister St.Pauli St.Pauli - Hamburg - Germany Andreas Wenk http://www.netzmeister-st-pauli.de http://blog.netzmeister-st-pauli.de mailto:a.w...@netzmeister-st-pauli.de -- 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] Slony-I timezone setting
On 29/06/2009 16:39, Pedro Doria Meunier wrote: btw: do you happen to know of a Slony mailing list? Here you go: http://lists.slony.info/mailman/listinfo Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Python client + select = locked resources???
On Mon, 2009-06-29 at 13:36 +0200, durumdara wrote:
I wanna ask something. I came from IB/FB world.
InterBase / FireBird ?
In this world I was everytime in transaction, because of reads are
also working under transactions.
Just like PostgreSQL. You can't run a query without a transaction in
PostgreSQL; if you don't issue an explicit BEGIN, it'll do an implicit
BEGIN/COMMIT around the statement.
In the FB world the transactions without any writes/updates are not
locking the database, so another clients can makes a transactions on
any records.
PostgreSQL doesn't lock the database for reads or writes. Transactions
do take out various levels of lock on tables when you do things with
those tables. See the locking documentation:
http://www.postgresql.org/docs/8.3/static/explicit-locking.html
Additionally, PostgreSQL can take out share and update locks against
rows, as the documentation mentions.
0.) I started Pylons web server, and in the browser I request for a
simple view (without modify anything).
1.) I opened PGAdmin.
2.) I move the focus to the table X.
3.) I opened an SQL editor and try to make two column adds:
alter table X add test_a date;
alter table X add test_b date;
ALTER TABLE does take out an exclusive lock on the table. See the
manual:
http://www.postgresql.org/docs/8.3/static/explicit-locking.html
If there's other work in progress, it can't get the exclusive lock until
that work completes.
And I wanna solve this problem, because if I must do some
modifications in the online database (for example: add a new field), I
don't want to shut down the webserver with all online clients...
You should not have to. If you can't get a lock on the table, then most
likely the web app is holding transactions open instead of opening them,
doing work, and promptly committing / rolling back.
Try connecting to the database with psql and running
select * from pg_stat_activity
while the web app is running. You should see only IDLE or working
connections, never idle in transaction. If you have anything idle in a
transaction for more than a few moments you WILL have problems, because
if those transactions have SELECTed from the table you're trying to
alter they'll hold share locks that will prevent ALTER TABLE from
grabbing an exclusive lock on the table.
cur = db.cursor()
cur.execute('select * from testtable')
rek = cur.fetchone()
cur.close()
while 1:
pass
Here you're holding a transaction open and idle. Wrong move. Close the
transaction (dispose the cursor) and then open a new transaction to do
more work.
--
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
Re: [GENERAL] GIN and GiST index - more in depth info
For sure I had a look to Oleg Bartunov' s and Teodor Sigaev's website at http://www.sai.msu.su/~megera/wiki/ but for me it's still not clear how to describe the differences between the indexes and the usage scenarios when to use GIN or GiST. As far as I understand it's a matter of usage scenario. GIN is extremely slow on updates, I seem to remember somewhere that it's actually often better to simply recreate the complete index than to update it; GiST's write performance is not half as bad. On the other hand, GIN is much faster on reads than GiST. If you've got some data that is read-only in nature, you'll probably fare better with GIN. If you need frequent updates, GiST ist the better choice. In certain scenarios you would use partial indexes to have a GiST index on current, still heavily updated data, and a GIN index on older, archived rows which are not updated any longer. Kind regards Markus Jede Stimme zahlt, jetzt voten fur die besten Games: www.bamaward.de Computec Media AG Sitz der Gesellschaft und Registergericht: Furth (HRB 8818) Vorstandsmitglieder: Albrecht Hengstenberg (Vorsitzender) und Rainer Rosenbusch Vorsitzender des Aufsichtsrates: Jurg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276 -- 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] pasting into psql garbles text
On Mon, Jun 29, 2009 at 10:49 AM, Cédric Villemain cedric.villem...@dalibo.com wrote: Le samedi 27 juin 2009, Merlin Moncure a écrit : Interestingly, the one platform that tends not to suffer from this is windows so I'm guessing this is a readline problem. Has anybody else noticed this? Is there a workaround? Whenever I have a huge chunk of text to paste into psql, I'll drop to an editor with \e, then paste it, the close the editor (I have $EDITOR=vim). Just a thought. --Scott
[GENERAL] permissions / ACLs made easier?
This idea is meant as an alternative to MySQL-style GRANT ... * or other similar permissions schemes. I posted a similar message on -hackers here: http://archives.postgresql.org/pgsql-hackers/2009-06/msg01393.php I'm posting on -general now to get some feedback from potential users to see if it actually solves problems for a significant group of people. The use case is an application with several roles like: * admin user - owns all the objects related to that application * normal user - INSERT/UPDATE/DELETE plus sequence usage * read-only user - for reporting The feature that I'm suggesting is a GRANT mask: [ not real syntax, just for illustration ] CREATE USER read_only_user GRANT (SELECT ON TABLE, USAGE ON SCHEMA) FROM admin_user; read_only_user would automatically have SELECT privileges on any table that admin_user has SELECT privileges on, and automatically have USAGE privileges on any schema that admin_user has privileges on. The benefits are: * you can create a new role after the fact, and you don't have to issue GRANT statements for every object in the database * you can create new objects without needing to issue appropriate GRANT statements for each user * you can easily see the permissions/ACLs you have set up without inspecting each object This scheme only helps when you have broad roles, like the admin/normal/read-only I listed above, and you don't complicate things with lots of exceptions. It's flexible enough that you can use it in interesting ways with groups and individual GRANT statements, but by that time the simplicity of this feature is most likely lost. With that in mind, who out there would really use this feature? 1. If you aren't using separate roles now, would you be more likely to do so with a feature like this? 2. If you are using multiple roles currently, would this feature simplify the management of those roles and their privileges? 3. If you are using an ORM, would this feature help you separate privileges better (include the name of the ORM)? 4. If you use GRANT ... * in MySQL, would this be an adequate substitute when using PostgreSQL? Regards, Jeff Davis -- 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] permissions / ACLs made easier?
On Mon, Jun 29, 2009 at 1:01 PM, Jeff Davis pg...@j-davis.com wrote: CREATE USER read_only_user GRANT (SELECT ON TABLE, USAGE ON SCHEMA) FROM admin_user; read_only_user would automatically have SELECT privileges on any table that admin_user has SELECT privileges on, and automatically have USAGE privileges on any schema that admin_user has privileges on. So, you're proposing the ability to inherit privileges from another user / role? That could be useful, but typically, when I have lots of roles hanging around, their privileges are mutually exclusive to the point where this won't help. The benefits are: * you can create a new role after the fact, and you don't have to issue GRANT statements for every object in the database Interesting for sure, but now in your example, I have to write a separate grant for the maybe 3 or 4 tables that shouldn't be read by read_only_user in the schema (i.e. ss #'s or other top-secret stuff that read_only_user shouldn't see). And if I'm a novice, I could easily get confused and give the world the ability to see what they really should not be seeing, just b/c I took the short route. Personally, I'd prefer being forced to write individual grants just to be sure I know what has privileges on what. * you can create new objects without needing to issue appropriate GRANT statements for each user One of the things I've always appreciated about pg is that you have to be explicit about your permissions. However, making things slightly easier isn't necessarily a bad thing. * you can easily see the permissions/ACLs you have set up without inspecting each object Maybe I'm missing this part of what you're proposing. Honestly, losing object level security is more a concern for me than being forced to write a pile of scripts. Maybe having a tool (like pgAdmin or pg_dump, something like pg_dump --privs_by_role rolename ) generate a sql script for the grants that a role has would be more appropriate than a core change. This scheme only helps when you have broad roles, like the admin/normal/read-only I listed above, and you don't complicate things with lots of exceptions. It's flexible enough that you can use it in interesting ways with groups and individual GRANT statements, but by that time the simplicity of this feature is most likely lost. Agreed. With that in mind, who out there would really use this feature? 1. If you aren't using separate roles now, would you be more likely to do so with a feature like this? Not likely, the people I've worked with in the past are in the routine as role / non-role shops based on dev practices, dba experience, etc... I think this would just be another feature that would get a 'huh, neat' type of response. 2. If you are using multiple roles currently, would this feature simplify the management of those roles and their privileges? Not really, as above, I think that most [well-designed] RBAC solutions have enough mutual exclusivity where permissions inheritance at the time of user creation may complicate issues. --Scott
Re: [GENERAL] Switching from MySQL: ON DUPLICATE KEY UPDATE, plpgsql function
On Sat, Jun 27, 2009 at 08:23:26PM -0400, APseudoUtopia wrote:
- Hey list,
-
- I'm migrating my site away from MySQL to PostgreSQL. So far, it's been
- going great. However, there's one problem I've been having trouble
- solving.
-
- I have a query which allows users to Catch up on read posts on the
- forum. It works by either updating or inserting the last post read
- number from every forum thread into the readposts table (for that
- userid and threadid combination, of course). Here's the table
- structure:
-
- CREATE TABLE forums_readposts (
- useridINTEGER NOT NULL REFERENCES users_main (id) ON DELETE
CASCADE,
- threadidINTEGER NOT NULL REFERENCES forums_topics (id) ON
- DELETE CASCADE,
- lastpostread INTEGER NOT NULL CHECK (lastpostread = 0),
- PRIMARY KEY (userid, threadid)
- );
-
- Here's the original MySQL query that I have (db_string is a php
- function that escapes the string):
-
- INSERT INTO forums_readposts (userid, threadid, lastpostread)
- SELECT ' . db_string($_SESSION['UserInfo']['id']) . ', id,
- lastpost FROM forums_topics ON DUPLICATE KEY UPDATE lastpostread
- = lastpost;
So regardless of other design issues. (i.e., assuming what you have was working
in MySQL).
Wouldn't you just be looking for something like:
BEGIN;
EXECUTE 'insert into forums_readposts values ('...')';
EXCEPTION when unique_violation THEN
EXECUTE 'update forums_readposts set lastpostread = '...' ';
END;
The logic as i read your post is. If the user's never done a catchup operation
before, this will create the record. If he has, then it will update this record
to reflect the new transid.
Dave
--
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] partitioning question -- how to guarantee uniqueness across partitions
On Jun 28, 2009, at 11:45 AM, Whit Armstrong wrote:
Thanks, Tom.
Let me give a little more detail on my actual data rather than the
simple example I sent.
I have a 60GB table of loan balances, which I've partitioned into 26
tables.
The loan id's are a sequence of 6 characters, so the partitioning rule
I've used is the first character of the loan id, which yields roughly
equal sized partitions of 2.8 GB or so.
Each loan can only have one balance per month, so the primary key on
each partition is set to be loan_id and asofdate.
However, this data is meant to be available via a rails application,
hence, the need for a surrogate key of integers which is unique across
the entire set of partitions.
Creation of new rows in the partitioned tables should not be an issue
under normal circumstances because I see that all of the child tables
use the same sequence for generating new id's.
However, what makes me nervous is that there is no explicit constraint
in the database that prevents duplicate id's from being created, and
I'm not sure how the rails app would react if for whatever reason
duplicate id keys wound up in the table.
As long as your inserts always use the default value,
nextval('sequence_name'), for the id values then that can never happen
unless you at some point use setval('sequence_name', X) where X = the
max value already present in your partitioned table, which you should
never be doing anyway.
Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL]
Hi Volk, at first sorry for my English. I use postgresql very often and I really love it but the syntax for outer join make me sick. Oracle short notation (+) is also not a best choice at this place but I recall me, that the Informix have a really good and clear syntax: select * from a, outer b where a.id = b.id; select * from a, outer( b, outer c) where a.id = b.id and b.id= c.id; And surely, I would like to see that also in postgresql. I hope, I can win you for that. Sincerely, LS
Re: [GENERAL]
On Mon, Jun 29, 2009 at 2:08 PM, littlesuspenselittlesuspe...@web.de wrote: Hi Volk, at first sorry for my English. I use postgresql very often and I really love it but the syntax for outer join make me sick. Oracle short notation (+) is also not a best choice at this place but I recall me, that the Informix have a really good and clear syntax: Note that the word outer is just noise in pgsql, i.e. it's not needed. What you've got are left outer, right outer, and full outer joins. All can be called just left, right, or full joins. Note that inner joins are just called joins. select * from a, outer b where a.id = b.id; select * from a full join b on (a.id=b.id) where ... select * from a left join b on (a.id=b.id) where ... select * from a join b on (a.id=b.id) where ... and so on. And surely, I would like to see that also in postgresql. What you get with postgresql is mostly ANSI standard stuff, which left/right/full outer and inner joins are. -- 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]
On Mon, Jun 29, 2009 at 5:11 PM, Scott Marlowescott.marl...@gmail.com wrote: On Mon, Jun 29, 2009 at 2:08 PM, littlesuspenselittlesuspe...@web.de wrote: Hi Volk, at first sorry for my English. I use postgresql very often and I really love it but the syntax for outer join make me sick. Oracle short notation (+) is also not a best choice at this place but I recall me, that the Informix have a really good and clear syntax: Note that the word outer is just noise in pgsql, i.e. it's not needed. What you've got are left outer, right outer, and full outer joins. All can be called just left, right, or full joins. Note that inner joins are just called joins. select * from a, outer b where a.id = b.id; select * from a full join b on (a.id=b.id) where ... select * from a left join b on (a.id=b.id) where ... select * from a join b on (a.id=b.id) where ... also, select * from a join b using(id) where...; In simple join cases this is usually the best way to go. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Database schema dumper
I'd like to dump a database schema to a file, probably XML but anything reasonable is good enough. By schema, I don't mean the narrow postgres keyword, but rather the table names, columns, foreignkeys, triggers, constraints, etc. I'd really like something that could work for other databases too, including O-, M, etc. But that might be asking too much. A quick google for variations on dump database schema didn't find much. Whether it be a CPAN module, Java class, etc, or a standalone program, none of that matters much. -- ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._. Felix Finch: scarecrow repairman rocket surgeon / fe...@crowfix.com GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933 I've found a solution to Fermat's Last Theorem but I see I've run out of room o -- 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] Database schema dumper
fe...@crowfix.com wrote on 30.06.2009 00:08: I'd like to dump a database schema to a file, probably XML but anything reasonable is good enough. By schema, I don't mean the narrow postgres keyword, but rather the table names, columns, foreignkeys, triggers, constraints, etc. I'd really like something that could work for other databases too, including O-, M, etc. But that might be asking too much. Take a look at my SQL Workbench/J, especially the WbReport command: http://www.sql-workbench.net/index.html http://www.sql-workbench.net/manual/wb-commands.html#command-schema-report Regards Thomas -- 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] Database schema dumper
On Mon, Jun 29, 2009 at 4:08 PM, fe...@crowfix.com wrote: I'd like to dump a database schema to a file, probably XML but anything reasonable is good enough. By schema, I don't mean the narrow postgres keyword, but rather the table names, columns, foreignkeys, triggers, constraints, etc. I'd really like something that could work for other databases too, including O-, M, etc. But that might be asking too much. Have you tried pg_dump -s yet? -- 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] Database schema dumper
On Mon, Jun 29, 2009 at 04:32:46PM -0600, Scott Marlowe wrote: Have you tried pg_dump -s yet? We I know I said the format is immaterial, and I know I could write something to convert it into something more useful, but it is on the low end of what I was looking for, and is very much PostgreSQL only, not any chance of converting it for use with other databases. -- ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._. Felix Finch: scarecrow repairman rocket surgeon / fe...@crowfix.com GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933 I've found a solution to Fermat's Last Theorem but I see I've run out of room o -- 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] Database schema dumper
On Tue, Jun 30, 2009 at 12:21:22AM +0200, Thomas Kellerer wrote: Take a look at my SQL Workbench/J, especially the WbReport command: http://www.sql-workbench.net/index.html http://www.sql-workbench.net/manual/wb-commands.html#command-schema-report That just may do the trick. I'll explore it a bit tonight. Looks like a lot of work has gone into it. -- ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._. Felix Finch: scarecrow repairman rocket surgeon / fe...@crowfix.com GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933 I've found a solution to Fermat's Last Theorem but I see I've run out of room o -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] High consumns memory
Hi all I have a software developed in Delphi as a Windows Service, but, i don't know why, it consumns an unexpected large system memory (515m). The service access PostgresSQL by ODBC driver (psqlodbc_08_03_0400) and it consist simply of a loop calling a procedure PL/PGSQL. How to discover what is causing or why this high memory usage ? What objects are being used on this session ? Software developed in Delphi 7 as a windows service. PostgresSQL 8.3.6 Database with PostGis extension Server p52a S.O.: Red Hat Enterprise Linux AS release 4 (Nahant Update 1) linux 2.6.9-11.EL #1 SMP ppc64 ppc64 ppc64 GNU/Linux S.O. information Date 29/06/2009 top - 07:58:49 up 21 days, 7:47, 1 user, load average: 0.73, 0.74, 0.71 Tasks: 131 total, 1 running, 129 sleeping, 0 stopped, 1 zombie Cpu(s): 13.2% us, 1.3% sy, 0.0% ni, 83.1% id, 1.9% wa, 0.2% hi, 0.2% si Mem: 4107392k total, 3764272k used, 343120k free,24760k buffers Swap: 2031608k total, 592k used, 2031016k free, 354k cached PID USER PR NI VIRT SWAP RES SHR DATA CODE S %CPU %MEMTIME+ COMMAND 9943 postgres 15 0 860m 41m 819m 811m 9604 3540 D 88.3 20.4 0:08.33 postgres: dbtest test 10.255.100.73(4796) SELECT 32731 postgres 16 0 854m 741m 112m 109m 3880 3540 S 12.9 2.8 11:52.47 postgres: dbtest test 10.255.100.65(57470) idle Date 29/06/2009 top - 10:37:11 up 21 days, 10:25, 1 user, load average: 1.50, 1.60, 1.46 Tasks: 130 total, 3 running, 126 sleeping, 0 stopped, 1 zombie Cpu(s): 13.3% us, 1.2% sy, 0.0% ni, 84.4% id, 0.7% wa, 0.2% hi, 0.2% si Mem: 4107392k total, 4103184k used, 4208k free,49036k buffers Swap: 2031608k total, 592k used, 2031016k free, 3698156k cached PID USER PR NI VIRT SWAP RES SHR DATA CODE S %CPU %MEMTIME+ COMMAND 9943 postgres 15 0 994m 33m 960m 818m 143m 3540 S 29.5 23.9 48:19.96 postgres: dbtest test 10.255.100.73(4796) idle 32731 postgres 16 0 854m 666m 188m 184m 3888 3540 R 25.5 4.7 25:03.44 postgres: dbtest test 10.255.100.65(57470) PARSE Date 29/06/2009 top - 19:05:03 up 21 days, 18:53, 1 user, load average: 0.95, 0.91, 0.90 Tasks: 131 total, 1 running, 129 sleeping, 0 stopped, 1 zombie Cpu(s): 9.2% us, 0.5% sy, 0.0% ni, 88.7% id, 1.2% wa, 0.3% hi, 0.2% si Mem: 4107392k total, 4094680k used,12712k free,18320k buffers Swap: 2031608k total, 592k used, 2031016k free, 3331036k cached PID USER PR NI VIRT RES SHR CODE DATA S %CPU %MEMTIME+ COMMAND 9943 postgres 16 0 1366m 1.3g 818m 3540 515m S 31.2 33.2 192:20.61 postgres: dbtest test 10.255.100.73(4796) SELECT 32731 postgres 16 0 853m 305m 302m 3540 3176 S 0.0 7.6 47:38.95 postgres: dbtest test 10.255.100.65(57470) idle As shown in column DATA(PID 9943) on 07:58:49 and on 19:05:03(515m) been a significant increase in the consumption of memory. postgresql.conf information: name |setting | unit -+--+-- archive_mode| on | autovacuum_analyze_scale_factor | 0.4 | autovacuum_analyze_threshold| 500 | autovacuum_vacuum_threshold | 1000 | checkpoint_segments | 15 | checkpoint_timeout | 1800 | s default_statistics_target | 50 | effective_cache_size| 249600 | 8kB fsync | on | logging_collector | on | maintenance_work_mem| 409600 | kB max_connections | 100 | max_fsm_pages | 3458000 | shared_buffers | 64000| 8kB wal_buffers | 100 | 8kB work_mem| 5120 | kB
[GENERAL] 64 Bit ODBC Drivers for windows
Is there anyway to connect to postgre using a 64 bit Windows OS? I am having problems connecting to a local DB since i have upgraded my computer. I would like to connect using C#.NET Any help would be appreciated. Drew
Re: [GENERAL] 64 Bit ODBC Drivers for windows
use the .net provider http://npgsql.projects.postgresql.org/ I'm not sure of a 64bit build. although a 32bit version should run on 64 bit windows without any problems. Message from mailto:drewtimm...@gmail.com Andrew Timmins drewtimm...@gmail.com at 06-29-2009 05:36:58 PM -- Is there anyway to connect to postgre using a 64 bit Windows OS? I am having problems connecting to a local DB since i have upgraded my computer. I would like to connect using C#.NET Any help would be appreciated. Drew
Re: [GENERAL] masking the code
On Mon, Jun 29, 2009 at 2:31 PM, Scott Meadscott.li...@enterprisedb.com wrote: As with many different types of security (i.e. the 3 foot high fence) this is really just a deterrent to most people who either aren't capable of reverse engineering or are just not interested in the first place. Someone I know used to work at a nuclear power and research facility (No Dave, someone else) and told me an interesting story. The facility in question was often beset by protesters who invariably managed to scale the perimeter fence. He asked one of the security guards one day why they didn't build a better fence since the run-of-the-mill fence was obviously not up to the task for such a large perimeter against determined foes. The answer was that the fence was not there to keep people out at all. It's purpose was in fact to ensure that when they prosecuted it would be impossible for anyone to claim they hadn't realized they were trespassing... -- greg http://mit.edu/~gsstark/resume.pdf -- 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] High consumns memory
On Mon, Jun 29, 2009 at 6:14 PM, Anderson Valadaresanderva...@gmail.com wrote: Hi all I have a software developed in Delphi as a Windows Service, but, i don't know why, it consumns an unexpected large system memory (515m). The service access PostgresSQL by ODBC driver (psqlodbc_08_03_0400) and it consist simply of a loop calling a procedure PL/PGSQL. How to discover what is causing or why this high memory usage ? What objects are being used on this session ? PID USER PR NI VIRT SWAP RES SHR DATA CODE S %CPU %MEM TIME+ COMMAND 9943 postgres 15 0 860m 41m 819m 811m 9604 3540 D 88.3 20.4 0:08.33 postgres: dbtest test 10.255.100.73(4796) SELECT 32731 postgres 16 0 854m 741m 112m 109m 3880 3540 S 12.9 2.8 11:52.47 postgres: dbtest test 10.255.100.65(57470) idle Generally speaking, the actual delta for memory usage is the res - shared memory, which puts both of those backends at using an individual amount of memory at somewhere in the 5 to 8 meg range. The rest is shared memory, including shared_buffers and such. Seeing as you say your shared_buffers is 512M, I'm not sure where the rest of the shared memory is coming from here in top. Mem: 4107392k total, 4103184k used, 4208k free, 49036k buffers Swap: 2031608k total, 592k used, 2031016k free, 3698156k cached Note that your machine is still showing 3.6G or so used for caching our of 4G, so you're only using an actual amount of about 400 Meg Are you having any measurable performance issues, or just curious / worried about what seems like high memory usage? Your numbers look pretty normal to me otherwise. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
