Re: [GENERAL] Problem with Crosstab (Concatenate Problem)
What version of PostgreSQL are you running? The error seems to indicate that you don't have the crosstab(text,text) form of the function. In psql do: contrib_regression=# \df crosstab List of functions Schema | Name | Result data type | Argument data types | Type +--+--+-+ public | crosstab | SETOF record | text| normal public | crosstab | SETOF record | text, integer | normal public | crosstab | SETOF record | text, text | normal (3 rows) Does it look like this? Ha, that's a thing! Indeed, it looks like this in my case: Schema | Name | Result data type | Argument data types | Type +--+--+-+ public | crosstab | SETOF record | text, integer | normal (1 row) I am running version 8.4.3, on Mac with Kingchaos libraries. Installed the contrib/crosstab myself afterwards. What can I do now to include the other functions too? Or is this only with newer versions of Crosstab? Thanks a lot for your help! Stef -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem with Crosstab (Concatenate Problem)
What version of PostgreSQL are you running? The error seems to indicate that you don't have the crosstab(text,text) form of the function. In psql do: contrib_regression=# \df crosstab List of functions Schema | Name | Result data type | Argument data types | Type +--+--+-+ public | crosstab | SETOF record | text| normal public | crosstab | SETOF record | text, integer | normal public | crosstab | SETOF record | text, text | normal (3 rows) Does it look like this? Ha, that's a thing! Indeed, it looks like this in my case: Schema | Name | Result data type | Argument data types | Type +--+--+-+ public | crosstab | SETOF record | text, integer | normal (1 row) I am running version 8.4.3, on Mac with Kingchaos libraries. Installed the contrib/crosstab myself afterwards. What can I do now to include the other functions too? Or is this only with newer versions of Crosstab? Ok, got it re-compiled and re-inserted, and now they are there, these functions. Thanks a lot for your help! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Linux
I know that this is probably a religion issue but we are looking to move Postgres to a Linux server. We currently have a Windows 2008 R2 active directory and all of the other servers are virtualized via VMWare ESXi. One of the reasons is that we want to use a 64 bit Postgres server and the UUID processing contrib module does not provide a 64 bit version for Windows. I would also assume that the database when properly tuned will probably run faster in a *inx environment. What and why should I look at certain distributions? It appears from what I read, Ubanta is a good desktop but not a server. Best Regards -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax
[GENERAL] check constraint on insert but not delete
This is a longshot, but here goes... Is there a way to require that a check constraint be checked on insert but not update?Worth knowing is that my check constraint runs a PLPgsql proc which returns a yes/no kinf of flag which the constraint proper checks. Thanks !
Re: [GENERAL] Linux
On Thu, Nov 4, 2010 at 8:00 AM, Michael Gould mgo...@intermodalsoftwaresolutions.net wrote: I know that this is probably a religion issue but we are looking to move Postgres to a Linux server. We currently have a Windows 2008 R2 active directory and all of the other servers are virtualized via VMWare ESXi. One of the reasons is that we want to use a 64 bit Postgres server and the UUID processing contrib module does not provide a 64 bit version for Windows. I would also assume that the database when properly tuned will probably run faster in a *inx environment. What and why should I look at certain distributions? It appears from what I read, Ubanta is a good desktop but not a server. Whilst I won't discourage you from a move to Linux, which I think is a good idea in general (and personally, my choice is RHEL - or CentOS if you want free - for a production server), I will note that Hiroshi Saito has ported ossp-uuid to Win64 now, and we're working on getting it included in the next update of PG 9.0. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Linux
On 11/4/2010 9:00 AM, Michael Gould wrote: What and why should I look at certain distributions? It appears from what I read, Ubanta is a good desktop but not a server. We use CentOS. I don't know of a good reason to look at other distributions for a server today. You may or may not see a performance difference. Typically the DB will perform the same on the same hardware regardless of OS, but there are a few reasons you might see differences at the margin or under specific loads. -- 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] Linux
In response to Michael Gould mgo...@intermodalsoftwaresolutions.net: I know that this is probably a religion issue but we are looking to move Postgres to a Linux server. We currently have a Windows 2008 R2 active directory and all of the other servers are virtualized via VMWare ESXi. One of the reasons is that we want to use a 64 bit Postgres server and the UUID processing contrib module does not provide a 64 bit version for Windows. I would also assume that the database when properly tuned will probably run faster in a *inx environment. What and why should I look at certain distributions? It appears from what I read, Ubanta is a good desktop but not a server. religion I use FreeBSD everywhere, and have over 10 years experience running PostgreSQL on FreeBSD ... I've been extremely happy with how well the two work together, including upgrade paths, performance, security, and customizability. I currently manage over 20 FreeBSD+PostgreSQL servers at work. /religion If you're married to Linux, remember that PostgreSQL has had a pretty tight relationship with Red Hat for a while now. Beyond that, I think that any Linux distro that caters to a server environment will work well for you. The thing (in my experience) that's going to make you happy or angry is how well the packaging system works. Find a distro whos packaging system keeps up to date with PostgreSQL releases and value adds stuff to make upgrading, management, and migration easier and you'll probably have a distro that you'll be happy with. -- 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
Re: [GENERAL] Linux
On 11/04/2010 11:10 AM, Bill Moran wrote: In response to Michael Gouldmgo...@intermodalsoftwaresolutions.net: I know that this is probably a religion issue but we are looking to move Postgres to a Linux server. We currently have a Windows 2008 R2 active directory and all of the other servers are virtualized via VMWare ESXi. One of the reasons is that we want to use a 64 bit Postgres server and the UUID processing contrib module does not provide a 64 bit version for Windows. I would also assume that the database when properly tuned will probably run faster in a *inx environment. What and why should I look at certain distributions? It appears from what I read, Ubanta is a good desktop but not a server. religion I use FreeBSD everywhere, and have over 10 years experience running PostgreSQL on FreeBSD ... I've been extremely happy with how well the two work together, including upgrade paths, performance, security, and customizability. I currently manage over 20 FreeBSD+PostgreSQL servers at work. /religion If you're married to Linux, remember that PostgreSQL has had a pretty tight relationship with Red Hat for a while now. Beyond that, I think that any Linux distro that caters to a server environment will work well for you. The thing (in my experience) that's going to make you happy or angry is how well the packaging system works. Find a distro whos packaging system keeps up to date with PostgreSQL releases and value adds stuff to make upgrading, management, and migration easier and you'll probably have a distro that you'll be happy with. We have used FreeBSD but are moving to CentOS. Main reason is longer support window. FreeBSD usually goes EOL in a year or two. CentOS 5.x is supported thru at least 2014. -- Stephen Clark *NetWolves* Sr. Software Engineer III Phone: 813-579-3200 Fax: 813-882-0209 Email: steve.cl...@netwolves.com http://www.netwolves.com
Re: [GENERAL] Linux
On Thu, Nov 04, 2010 at 11:10:24AM -0400, Bill Moran wrote: Beyond that, I think that any Linux distro that caters to a server environment will work well for you. The thing (in my experience) that's going to make you happy or angry is how well the packaging system works. Find a distro whos packaging system keeps up to date with PostgreSQL releases and value adds stuff to make upgrading, management, and migration easier and you'll probably have a distro that you'll be happy with. With this argument in mind: Debian/Testing has very good packages and support. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- 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] PHP Web Auditing and Authorization
On Wed, Nov 3, 2010 at 1:04 PM, Gabriel Dinis
gabriel.di...@vigiesolutions.com wrote:
Dear all,
Imagine I have two users Maria and Ana using a PHP site.
There is a common Postgres user phpuser for both.
I'm creating audit tables to track the actions made by each PHP site user.
(...)
Everything seems to wok fine except the *use*r information I'm getting, in
this case *phpuse*r.
I would like to have not the postgres user but the PHP site user (*Maria
or Ana*).
How can I pass the PHP site user (Maria or Ana) into Postgres in a clever
way?
I have done several web searches and found nothing for Postgres. I found a
solution for oracle:
http://www.oracle.com/technetwork/articles/dsl/php-web-auditing-171451.html
*
They use a client identifier feature.* Is there a similar way to do this
in Postgres?
I have a different approach than what people are suggesting here.
I have a first audit table that receives an entry for each page loaded by a
user. So each time I initialize my database connection, I create an entry in
that table. That table has a SERIAL column.
For each action that needs auditing, I have a trigger. That trigger calls
CURRVAL('serial_sequence') and stores that in the second audit table. This
way you can find out afterwards who did the action. The nice thing about
this approach is that you can see which actions were done in the same page:
it gives context to some operations that would be difficult to understand
otherwise.
Be sure to set autocommit off and commit or abort at the end of each page,
otherwise it will mix things up (especially when you use persistent
connections)!
Afterwards I clean up/aggregate unimportant actions (like pages that only do
SELECTs) so to keep the impact on database size low.
Kind regards,
Mathieu
Re: [GENERAL] check constraint on insert but not delete
On Thu, Nov 4, 2010 at 11:03 AM, Gauthier, Dave dave.gauth...@intel.com wrote: Is there a way to require that a check constraint be checked on insert but not update? Worth knowing is that my check constraint runs a PLPgsql proc which returns a yes/no kinf of flag which the constraint proper checks. Use an explicit ON INSERT trigger that calls your procedure instead of a check constraint. -- 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] check constraint on insert but not delete
On Thu, Nov 4, 2010 at 8:03 AM, Gauthier, Dave dave.gauth...@intel.com wrote: Is there a way to require that a check constraint be checked on insert but not update? Worth knowing is that my check constraint runs a PLPgsql proc which returns a yes/no kinf of flag which the constraint proper checks. I think by definition, Check constraints cannot differentiate between an update or insert. Its only concern is if the data is valid or not. If you need this flexibility, you should probably look at check constraints: http://www.postgresql.org/docs/9.0/interactive/sql-createconstraint.html -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- 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] Linux
On Thu, Nov 4, 2010 at 9:00 AM, Michael Gould mgo...@intermodalsoftwaresolutions.net wrote: I know that this is probably a religion issue but we are looking to move Postgres to a Linux server. We currently have a Windows 2008 R2 active directory and all of the other servers are virtualized via VMWare ESXi. One of the reasons is that we want to use a 64 bit Postgres server and the UUID processing contrib module does not provide a 64 bit version for Windows. I would also assume that the database when properly tuned will probably run faster in a *inx environment. What and why should I look at certain distributions? It appears from what I read, Ubanta is a good desktop but not a server. I've used RHEL, Centos, and Ubuntu as postgresql servers. Latest servers are Ubuntu because I needed a stable release with a late model kernel to support and scale on 48 cores. That said there were some serious bumps in the road to getting 10.04 to work on our servers. -- 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] Linux
On 4 November 2010 15:00, Michael Gould mgo...@intermodalsoftwaresolutions.net wrote: I know that this is probably a religion issue but we are looking to move Postgres to a Linux server. We currently have a Windows 2008 R2 active directory and all of the other servers are virtualized via VMWare ESXi. One of the reasons is that we want to use a 64 bit Postgres server and the UUID processing contrib module does not provide a 64 bit version for Windows. I would also assume that the database when properly tuned will probably run faster in a *inx environment. Let's not make the mistake of assuming that Windows and Linux are more or less comparable as Postgres platforms - they aren't. Most large installations are *nix based, and many tuning guides assume that you are using some *nix flavour, or mention windows only very briefly. I'm not sure of the details, but the windows System V IPC compatibility layer (or whatever it's called) that we ship + windows, simply don't work as well as native System V IPC running on the same hardware. This is why users are encouraged to try lower shared_buffers settings on windows - better results are attained on that platform by using proportionally more file system/OS cache. However, it is worth acknowledging that there has been some excellent work towards getting Postgres to work well on Windows, which it now does. I can personally attest to that. -- Regards, Peter Geoghegan -- 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] Linux
On Thu, Nov 4, 2010 at 11:23 AM, Steve Clark scl...@netwolves.com wrote: We have used FreeBSD but are moving to CentOS. Main reason is longer support window. FreeBSD usually goes EOL in a year or two. CentOS 5.x is supported thru at least 2014. FreeBSD 6.x was released in 2005 and was EOL'd finally last month. FreeBSD 7.x was released in Feb 2008 and has no EOL yet. It will be at minimum 2013 since the 7.4 release will be out next year. I guess if you need more than a 5 year support window it may make sense, but otherwise that doesn't seem like a reasonable argument to switch the whole OS. The only legitimate reason to switch the OS, IMHO, is operational experience of the people running 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] Linux
On 2010-11-04 16.00, Michael Gould wrote: I know that this is probably a religion issue but we are looking to move Postgres to a Linux server. We currently have a Windows 2008 R2 active directory and all of the other servers are virtualized via VMWare ESXi. One of the reasons is that we want to use a 64 bit Postgres server and the UUID processing contrib module does not provide a 64 bit version for Windows. I would also assume that the database when properly tuned will probably run faster in a *inx environment. What and why should I look at certain distributions? It appears from what I read, Ubanta is a good desktop but not a server. We're running Gentoo which is kind of unortodox but we're using the gentoo portage system for deploy of our own software and have extensive in-house experience with Gentoo. I wouldn't recommend it as a first time linux install though. -- Regards, Robert roppert Gravsjö -- 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] Linux
mgo...@intermodalsoftwaresolutions.net (Michael Gould) writes: What and why should I look at certain distributions? It appears from what I read, Ubanta is a good desktop but not a server. There are Ubuntu versions that don't promise support (e.g. - ongoing bug security fixes, and such) for nearly as long as one might like. The sorts of distributions that do promise such things for longer include: - Red Hat RHAS and such; - OpenSuSE; - CentOS; - Debian You'll find people that are fans of each of these. Not knowing any particular basis to infer your preferences (and you mayn't be aware of such, either!), it's tough to give any strong suggestions. I don't think you'd be steered woefully wrong with any of them. -- I have traveled the length and breadth of this country and talked with the best people, and can assure you that data processing is a fad that won't last out the year. -- Business books editor, Prentice Hall 1957 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] problem with select
Dear group:
I have a table structure like following:
city:
city_blockage_from age_to name
SF 10 20grade1
SF 21 30grade1
SF 35 40grade1
SF 53 19grade2
SF 100 153 grade2
NY 20 21 grade5
mydata:
samplecity_blockage_fromage_to baseo basen
1 SF 13 14 T Y
1 SF 33 34 A M
2 SF 24 25 G A
2 SF 18 19 G K
2 SF 33 34 A M
3 SF 13 14 T Y
3 SF 105 106 C T
I am interested in following result:
1. sample 1 and 3 share a same mydata.age_from and mydata.age_to (but
sample 2 and sample 3 should not have same age_from and age_to for
same city.name)
2. sample 1 and 2 share a same mydata.age_from and mydata.age_to
3. in the results basen should not be any of 'A' or 'T' or 'G' or 'C'.
that means for a give city.name sample 1 should contain both age_from
and age_to with sample 2 and sample 3. But sample 2 and sample 3
should have different age_from and age_to for same city.name.
myquery:
SELECT DISTINCT city.name from mydata,city WHERE mydata.sample = 1 AND
mydata.age_from = city.age_from AND mydata.age_to = city.age_to
INTERSECT
SELECT DISTINCT city.name from mydata,city WHERE mydata.sample = 2 AND
mydata.age_from = city.age_from AND mydata.age_to = city.age_to
INTERSECT
SELECT DISTINCT city.name from mydata,city WHERE mydata.sample = 3 AND
mydata.age_from = city.age_from AND mydata.age_to = city.age_to
INTERSECT AND
basen not in ('A', 'T', 'G','C');
I am not convinced that this is correct. can any one help me here please.
thanks
adrian
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Please Help...
Hi I'm sure you have had this question many times before but I feel as though I have genuinely exhausted all of my option and followed all the advise I can find online. During installation of Postgresql at the time when it tries to create the account I get the message ' user account postgress cannot be created because the password is too short or not complex enough' at which point the install is terminated. This is driving me nuts as it even says this when it chooses the password for you which just seems bizarre. I have literally been researching this for weeks! Now I feel I have reached a point where this has become an impossible task so I need help from the experts please :] Many thanks in advance and sorry if you have been asked this a million times Gav
Re: [GENERAL] Linux
Date: Thu, 4 Nov 2010 11:23:07 -0400 From: scl...@netwolves.com To: wmo...@potentialtech.com CC: mgo...@intermodalsoftwaresolutions.net; pgsql-general@postgresql.org Subject: Re: [GENERAL] Linux On 11/04/2010 11:10 AM, Bill Moran wrote: () We have used FreeBSD but are moving to CentOS. Main reason is longer support window. FreeBSD usually goes EOL in a year or two. CentOS 5.x is supported thru at least 2014. I am sorry, but why do you say that FreeBSD goes EOL in a year or two? FreeBSD system is not running like that. Note: I am not telling that CentOS is good or not good, just that the FreeBSD EOL is not that soon, usually is 5 or more years, take a look at that. -- Stephen Clark NetWolves Sr. Software Engineer III Phone: 813-579-3200 Fax: 813-882-0209 Email: steve.cl...@netwolves.com http://www.netwolves.com Andre.
Re: [GENERAL] Linux
I would recommend Ubuntu Server 10.04 LTS (long time support - 5 years for ongoing bug security fixes, and such). Also, Ubuntu is in focus now, has great community and a most of recent books on Linux target Ubuntu (which is valid factor for educating people on new platform). Ubuntu is great for first linux install. I've tried other distros, but I'm working on and recommending Ubuntu. I'm currently testing Ubuntu Server x64 10.04 LTS with pg 9.0 and looks good so far. On Thu, Nov 4, 2010 at 4:44 PM, Chris Browne cbbro...@acm.org wrote: mgo...@intermodalsoftwaresolutions.net (Michael Gould) writes: What and why should I look at certain distributions? It appears from what I read, Ubanta is a good desktop but not a server. There are Ubuntu versions that don't promise support (e.g. - ongoing bug security fixes, and such) for nearly as long as one might like. The sorts of distributions that do promise such things for longer include: - Red Hat RHAS and such; - OpenSuSE; - CentOS; - Debian You'll find people that are fans of each of these. Not knowing any particular basis to infer your preferences (and you mayn't be aware of such, either!), it's tough to give any strong suggestions. I don't think you'd be steered woefully wrong with any of them. -- I have traveled the length and breadth of this country and talked with the best people, and can assure you that data processing is a fad that won't last out the year. -- Business books editor, Prentice Hall 1957 -- 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] Please Help...
Now if you told people the OS, and the version of Postgres maybe ... ? -- 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] Please Help...
On Thu, Nov 4, 2010 at 1:58 AM, Gavin Burrows gavin_burr...@yahoo.com wrote: Hi I'm sure you have had this question many times before but I feel as though I have genuinely exhausted all of my option and followed all the advise I can find online. During installation of Postgresql at the time when it tries to create the account I get the message ' user account postgress cannot be created because the password is too short or not complex enough' at which point the install is terminated. This is driving me nuts as it even says this when it chooses the password for you which just seems bizarre. If it's generating the password for you, I assume you're using the older MSI installers which are largely obsolete now? In any case, the issue is that for some reason, Windows is refusing to allow the user account to be created. That's normally because it's not in compliance with a security policy on the system - almost always related to password complexity, or age/reuse. You could try creating the account manually in advance. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Please Help...
Hi Gavin, On 2010/11/04, at 17:58, Gavin Burrows gavin_burr...@yahoo.com wrote: I'm sure you have had this question many times before but I feel as though I have genuinely exhausted all of my option and followed all the advise I can find online. During installation of Postgresql at the time when it tries to create the account I get the message ' user account postgress cannot be created because the password is too short or not complex enough' at which point the install is terminated. This is driving me nuts as it even says this when it chooses the password for you which just seems bizarre. Which tool are you using to install PostgreSQL? Are you trying to install from source or some binary package? And which platform are you using? Windows? I guess I need to know which component produces the message. Regards, -- NAGAYASU Satoshi satoshi.nagay...@gmail.com
Re: [GENERAL] Linux
On Thu, Nov 4, 2010 at 10:45 AM, Esmin Gracic esmin.gra...@gmail.com wrote: I would recommend Ubuntu Server 10.04 LTS (long time support - 5 years for ongoing bug security fixes, and such). Also, Ubuntu is in focus now, has great community and a most of recent books on Linux target Ubuntu (which is valid factor for educating people on new platform). Ubuntu is great for first linux install. I've tried other distros, but I'm working on and recommending Ubuntu. I'm currently testing Ubuntu Server x64 10.04 LTS with pg 9.0 and looks good so far. Do yourself a favor and remove the ureadahead package now before you experience the heartache I had after getting a server up, configured, ready to go, and then have it not be able to boot because of it. They may have fixed that nasty bug by now, but if not, it's pretty horrific to have your new server refuse to boot because it has no GUI... (long story) -- 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] Linux
Whilst I won't discourage you from a move to Linux, which I think is a good idea in general (and personally, my choice is RHEL - or CentOS if you want free - for a production server), I will note that Hiroshi Saito has ported ossp-uuid to Win64 now, and we're working on getting it included in the next update of PG 9.0. That is good news, but I'm still thinking of moving to Linux because it appears that much more tuning can be accomplished and that you don't get the kitchen sink when you don't need it. Best Regard -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- 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] Linux
Thanks for all of the information. I will now need to spend some time looking at the various distributions that were mentioned here. Best Regards -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- 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] Save and load jpg in a PostgreSQL database
Hey Fernando, If you need to store binary data in a table you should use bytea data type. Than, in case of libpq: If you want to transmit binary data from client to server in text format you must prepare (escape) it for including into you SQL command (e.g., INSERT). If you can transmit the data from client to server in binary format you don't need escape you binary data, but you must tell libpq (or the library you use) that you transmission will be in a binary format. What library do you use to work with PostgreSQL ? 2010/11/4 lfmartinelli luis.fernando.martine...@gmail.com Hi, My name is Fernando, i work with C# and PostgreSQL. I need save a jpg in a PostgreSQL table. How do i this? I read in a forum to use this to save in database: INSERT INTO table (image) VALUES (pg_escape_bytea(image.jpg)) Don't i need convert image file to binary before save in a database? How get i this image to a variable using c#? Please help me! Thanks. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Save-and-load-jpg-in-a-PostgreSQL-database-tp3249969p3249969.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 -- // Dmitriy.
[GENERAL] Installing PostgreSQL on Windows 7 64-bit system
Hi, I've read through numerous forumshttp://forums.enterprisedb.com/posts/list/2328.pageto get past the following error An error occurred executing the Microsoft VC++ runtime installer. As far as I can tell, the error can arise if user privileges are not correctly set or if a firewall or anti-virus software is interrupting the install. Having said that, I have tried a few work-arounds but need a systematic way to get this installed. Please direct me to the correct forums or troubleshooting required to install postgres. Thanks a lot, Chris
Re: [GENERAL] Linux
On 11/04/2010 04:00 PM, Michael Gould wrote: I know that this is probably a religion issue but we are looking to move Postgres to a Linux server. We currently have a Windows 2008 R2 active directory and all of the other servers are virtualized via VMWare ESXi. One of the reasons is that we want to use a 64 bit Postgres server and the UUID processing contrib module does not provide a 64 bit version for Windows. I would also assume that the database when properly tuned will probably run faster in a *inx environment. What and why should I look at certain distributions? It appears from what I read, Ubanta is a good desktop but not a server. Best Regards Just find one that ships with the latest PG, to save you some work. Unless you plan to compile install PG manually, in that case, any major distribution would do. For production use, how long your version will be supported for (security updates) is likely to be the most important item in your checklist. I use CentOS. .TM. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Views - Under the Hood
Greetings: Lately, I've begun using views quite often especially when queries for various reports, etc. become complicated. I am now wondering if there is a price to pay in terms of overhead for this. In truth, I don't really understand how a view works. I know that it takes on many of the attributes of a table, but is it a table? Is the data pulled together when one selects from the view or is it maintained as a table all along. Guidance to the ignorant appreciated... -- Terry Lee Tucker tel: (336) 372-5432; cell: (336) 404-6897 te...@chosen-ones.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] Linux
On Thu, Nov 4, 2010 at 12:18 PM, Marco Colombo pg...@esiway.net wrote: On 11/04/2010 04:00 PM, Michael Gould wrote: I know that this is probably a religion issue but we are looking to move Postgres to a Linux server. We currently have a Windows 2008 R2 active directory and all of the other servers are virtualized via VMWare ESXi. One of the reasons is that we want to use a 64 bit Postgres server and the UUID processing contrib module does not provide a 64 bit version for Windows. I would also assume that the database when properly tuned will probably run faster in a *inx environment. What and why should I look at certain distributions? It appears from what I read, Ubanta is a good desktop but not a server. Best Regards Just find one that ships with the latest PG, to save you some work. Unless you plan to compile install PG manually, in that case, any major distribution would do. For production use, how long your version will be supported for (security updates) is likely to be the most important item in your checklist. I use CentOS. Note that if you'll be running in a mixed server environment, and you want to use slony replication, it's a good idea to just build pgsql and slony from source.For instance on Ubuntu (and i'd assume all debian systems) the pg_config is always from the latest pg version supported by that distro. slony can't properly build on those machines against anything but the latest release. Also, it allows you to make sure of things like int dates are on all machines, etc. Where I work we have older db servers running Centos and newer ones running Ubuntu, and the only way to get slony and pg 8.3 happy there was building from source. Luckily with pgsql it's a freaking snap to have a configure.local with all the switches for slony and postgresql ready to go. -- 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] Views - Under the Hood
On Thu, Nov 4, 2010 at 12:43 PM, Terry Lee Tucker te...@chosen-ones.org wrote: Greetings: Lately, I've begun using views quite often especially when queries for various reports, etc. become complicated. I am now wondering if there is a price to pay in terms of overhead for this. In truth, I don't really understand how a view works. I know that it takes on many of the attributes of a table, but is it a table? Is the data pulled together when one selects from the view or is it maintained as a table all along. Guidance to the ignorant appreciated... In pgsql a view is actually a rule that fires off the original query for you. So it's a simple wrapper, and is the same, for the most part, as simply typing in the original query again. So, it's pretty simple, and there's no real overhead to worry about. -- 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] Views - Under the Hood
On Thursday, November 04, 2010 15:03:49 Scott Marlowe wrote: On Thu, Nov 4, 2010 at 12:43 PM, Terry Lee Tucker te...@chosen-ones.org wrote: Greetings: Lately, I've begun using views quite often especially when queries for various reports, etc. become complicated. I am now wondering if there is a price to pay in terms of overhead for this. In truth, I don't really understand how a view works. I know that it takes on many of the attributes of a table, but is it a table? Is the data pulled together when one selects from the view or is it maintained as a table all along. Guidance to the ignorant appreciated... In pgsql a view is actually a rule that fires off the original query for you. So it's a simple wrapper, and is the same, for the most part, as simply typing in the original query again. So, it's pretty simple, and there's no real overhead to worry about. Thank you Scott! This is exactly what I needed to know... -- Terry Lee Tucker tel: (336) 372-5432; cell: (336) 404-6897 te...@chosen-ones.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] Views - Under the Hood
te...@chosen-ones.org (Terry Lee Tucker) writes: Lately, I've begun using views quite often especially when queries for various reports, etc. become complicated. I am now wondering if there is a price to pay in terms of overhead for this. In truth, I don't really understand how a view works. I know that it takes on many of the attributes of a table, but is it a table? Is the data pulled together when one selects from the view or is it maintained as a table all along. Guidance to the ignorant appreciated... Under the hood, views represent a rewriting of the query. http://www.postgresql.org/docs/8.4/static/rules-views.html If you have two tables that are joined together, in a view, then when you query the view, you're really running a more complex query than you're seeing, namely one that joins together the two tables, and does whatever else you put into your query. It *looks* like a table, for almost all intents and purposes, but what it is, really, is a structure that leads to your queries being rewritten to access the *real* tables that underly the view. So the date is, as you suggest, pulled together when one selects from the view. -- output = reverse(moc.liamg @ enworbbc) http://www3.sympatico.ca/cbbrowne/slony.html People are more vocally opposed to fur than leather because it's easier to harass rich women than motorcycle gangs. [bumper sticker] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] INSERT trigger into partitioned table
using PostgreSQL 8.4.5 on Ubuntu 10.04.1 LTS
I'm trying to create an INSERT trigger (plpgsql) based on the example
provided here:
http://www.postgresql.org/docs/current/interactive/ddl-partitioning.html
to automatically insert data into the currect yearly table partition.
For some reason, it puts double quotes on my timestamp values which
causes the INSERT to fail. I'd rather not list all the NEW.columns, as
i have 50+ columns and I'm hoping to use this function on several
different tables that have completely different columns.
Data is imported like this:
INSERT INTO master VALUES ('2010-308 1455',
296.32,311,1.4732,.01182,1.6943,.32264,8.02,8.71,9.11,8.84,11.59,-.196,-
.027,.227,.474,.011,.015,.006,.013,.007,.008,.024,.013,.016,.001,.001,29
6.7,311.6,1.486,.051,1.707,.367,8.05,8.72,9.13,8.86,11.61,-.194,-.025,29
5.4,309.1,1.433,-.012,1.682,.286,8,8.68,9.04,8.8,11.51,-.198,-.029);
The first column is a date/time which psql interprets correctly into a
timestamp (I use this in several other scripts using non-partitioned
tables that work fine).
For my non-partitioned tables, this works perfectly using a trigger with
INSERT INTO new_table SELECT NEW.*; but not when I switch to a
dynamic EXECUTE statement (see bellow)
No matter what I do, I can't get ride of the double quotes (or replace
them with single quotes); see below for output. I've tried NEW.datetime
= to_char( NEW.datetime,'-MM-DD HH24:MI:SS');
and
NEW.datetime = quote_nullable( NEW.datetime );
and
NEW.station = quote_literal(NEW.station);
and whatever else i could think of. What am I missing / doing wrong?
Function code :
BEGIN
-- The table we'll inherit from
ourMasterTable := 'master';
-- Get the partition table names ~ master_year
SELECT ourMasterTable|| '_' || EXTRACT(ISOYEAR FROM
NEW.datetime) into ourTable;
-- had to do this : EXECUTE will fail if i use NEW.* in
ourInsertSTMT
SELECT NEW.* into new_row;
RAISE NOTICE '%',new_row;
-- Create our insert statement
ourInsertSTMT := 'INSERT INTO '|| ourTable || ' VALUES( ' ||
new_row || ')';
--Try execute it
EXECUTE ourInsertSTMT;
RETURN NULL;
EXCEPTION
WHEN OTHERS THEN
RAISE NOTICE 'NUM:%, DETAILS:%', SQLSTATE, SQLERRM;
...
RAISE NOTICE 'Error inserting into existing partition % for
%',ourTable,ourInsertSTMT;
END;
---
result:
NOTICE: (2010-11-04
14:55:00,296.32,311,1.4732,0.01182,1.6943,0.32264,8.02,8.71,9.11,8.84,1
1.59,-0.196,-0.027,0.227,0.474,0.011,0.015,0.006,0.013,0.007,0.008,0.024
,0.013,0.016,0.001,0.001,296.7,311.6,1.486,0.051,1.707,0.367,8.05,8.72,9
.13,8.86,11.61,-0.194,-0.025,295.4,309.1,1.433,-0.012,1.682,0.286,8,8.68
,9.04,8.8,11.51,-0.198,-0.029)
NOTICE: NUM:42703, DETAILS:column 2010-11-04 14:55:00 does not exist
NOTICE: Error inserting into existing partition master_2010 for INSERT
INTO master_2010 VALUES( (2010-11-04 14:55:00,
296.32,311,1.4732,0.01182,1.6943,0.32264,8.02,8.71,9.11,8.84,11.59,-0.19
6,-0.027,0.227,0.474,0.011,0.015,0.006,0.013,0.007,0.008,0.024,0.013,0.0
16,0.001,0.001,296.7,311.6,1.486,0.051,1.707,0.367,8.05,8.72,9.13,8.86,1
1.61,-0.194,-0.025,295.4,309.1,1.433,-0.012,1.682,0.286,8,8.68,9.04,8.8,
11.51,-0.198,-0.029))
Re: [GENERAL] Views - Under the Hood
On 04/11/2010 19:58, Chris Browne wrote: Under the hood, views represent a rewriting of the query. http://www.postgresql.org/docs/8.4/static/rules-views.html If you have two tables that are joined together, in a view, then when you query the view, you're really running a more complex query than you're seeing, namely one that joins together the two tables, and does whatever else you put into your query. It *looks* like a table, for almost all intents and purposes, but what it is, really, is a structure that leads to your queries being rewritten to access the *real* tables that underly the view. Besides not being able to write to views without adding extra rules, are there are other intents and purposes for which a view doesn't look like a table? Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Views - Under the Hood
One of the benefits of writing views instead of using SQL in your code, is that any developer or developer tool can use the view. So the DB developer writes the view and maybe define indexes that can speed up the query and any developer of any software that uses the DB can refer to the View instead of writing the SQL. Moreover if sw developers use tools like ORMs those toolo can take advantage of the View and write all the code to use them to speed up the sw developer work. Il 04/11/2010 20.24, Terry Lee Tucker ha scritto: On Thursday, November 04, 2010 15:03:49 Scott Marlowe wrote: On Thu, Nov 4, 2010 at 12:43 PM, Terry Lee Tucker te...@chosen-ones.org wrote: Greetings: Lately, I've begun using views quite often especially when queries for various reports, etc. become complicated. I am now wondering if there is a price to pay in terms of overhead for this. In truth, I don't really understand how a view works. I know that it takes on many of the attributes of a table, but is it a table? Is the data pulled together when one selects from the view or is it maintained as a table all along. Guidance to the ignorant appreciated... In pgsql a view is actually a rule that fires off the original query for you. So it's a simple wrapper, and is the same, for the most part, as simply typing in the original query again. So, it's pretty simple, and there's no real overhead to worry about. Thank you Scott! This is exactly what I needed to know... -- == dott. Ivano Mario Luberti Archimede Informatica societa' cooperativa a r. l. Sede Operativa Via Gereschi 36 - 56126- Pisa tel.: +39-050- 580959 tel/fax: +39-050-9711344 web: www.archicoop.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] pg_hba LDAP Authentication syntax
Howdy, I was hoping someone could help me with ye olde ldap authentication syntax. I'm currently using PG 8.3.9 and an upgrade is not an option. Now, that being said, since i'm very new to LDAP i decided to use PG 9 to experiment with since it looks like it has an easier syntax. So what i've got working in PG9 is the following: hba stuff ldap ldapserver=w.x.y.z ldapbinddn=cn=admin,dc=domain,dc=com ldapbindpasswd=password ldapbasedn=ou=postgresql,dc=domain,dc=com I'm trying to translate that to the old syntax of: hba stuff ldap ldap://w.x.y.z/ou=postgresql,dc=domain,dc=com;stuff basically, i don't know how to fit cn=admin and ldapbindpassword into that string. Thanks 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] pg_hba LDAP Authentication syntax
On Thu, Nov 4, 2010 at 13:54, David Kerr d...@mr-paradox.net wrote: Howdy, I was hoping someone could help me with ye olde ldap authentication syntax. I'm currently using PG 8.3.9 and an upgrade is not an option. Now, that being said, since i'm very new to LDAP i decided to use PG 9 to experiment with since it looks like it has an easier syntax. So what i've got working in PG9 is the following: hba stuff ldap ldapserver=w.x.y.z ldapbinddn=cn=admin,dc=domain,dc=com ldapbindpasswd=password ldapbasedn=ou=postgresql,dc=domain,dc=com I'm trying to translate that to the old syntax of: hba stuff ldap ldap://w.x.y.z/ou=postgresql,dc=domain,dc=com;stuff basically, i don't know how to fit cn=admin and ldapbindpassword into that string. The search+bind feature is not available on 8.3 - it's a new feature in 9.0. -- 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] pg_hba LDAP Authentication syntax
On Thu, Nov 04, 2010 at 02:07:29PM -0700, Magnus Hagander wrote: - - I'm trying to translate that to the old syntax of: - hba stuff ldap ldap://w.x.y.z/ou=postgresql,dc=domain,dc=com;stuff - - basically, i don't know how to fit cn=admin and ldapbindpassword into that string. - - The search+bind feature is not available on 8.3 - it's a new feature in 9.0. Not 8.4? http://www.postgresql.org/docs/8.4/interactive/auth-methods.html 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] pg_hba LDAP Authentication syntax
On Thu, Nov 4, 2010 at 15:30, David Kerr d...@mr-paradox.net wrote: On Thu, Nov 04, 2010 at 02:07:29PM -0700, Magnus Hagander wrote: - - I'm trying to translate that to the old syntax of: - hba stuff ldap ldap://w.x.y.z/ou=postgresql,dc=domain,dc=com;stuff - - basically, i don't know how to fit cn=admin and ldapbindpassword into that string. - - The search+bind feature is not available on 8.3 - it's a new feature in 9.0. Not 8.4? http://www.postgresql.org/docs/8.4/interactive/auth-methods.html No. 8.4 had only the prefix/suffix method, the search/bind method was added in 9.0.That's why the parameters for search/bind don't exist in 8.4. -- 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] pg_hba LDAP Authentication syntax
On Thu, Nov 04, 2010 at 03:35:11PM -0700, Magnus Hagander wrote: - On Thu, Nov 4, 2010 at 15:30, David Kerr d...@mr-paradox.net wrote: - On Thu, Nov 04, 2010 at 02:07:29PM -0700, Magnus Hagander wrote: - - - - I'm trying to translate that to the old syntax of: - - hba stuff ldap ldap://w.x.y.z/ou=postgresql,dc=domain,dc=com;stuff - - - - basically, i don't know how to fit cn=admin and ldapbindpassword into that string. - - - - The search+bind feature is not available on 8.3 - it's a new feature in 9.0. - - Not 8.4? - http://www.postgresql.org/docs/8.4/interactive/auth-methods.html - - - No. 8.4 had only the prefix/suffix method, the search/bind method was - added in 9.0.That's why the parameters for search/bind don't exist in - 8.4. Ok thanks. Dave -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Streaming replication + pgpool-II tutorial
Hi, It seems there are some demand to seek how to deal with automated failover while using Streaming replication and Hot standby. I wrote a small tutorial how to implement this by using pgpool-II. Please visit: http://pgpool.projects.postgresql.org/contrib_docs/simple_sr_setting/index.html if you are interested. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Changing boolean to a smallint
I have a table column I want to change from a boolean to a smallint. changing false to 0 and true to 1. How do I do that? Christine Penner Ingenious Software 250-352-9495 ch...@fp2.ca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Changing boolean to a smallint
On 2010-11-04 15:41, Christine Penner wrote: I have a table column I want to change from a boolean to a smallint. changing false to 0 and true to 1. How do I do that? Christine Penner Ingenious Software 250-352-9495 ch...@fp2.ca ALTER TABLE ALTER col_name TYPE SMALLINT USING CASE WHEN col_name THEN 1 ELSE 0 END; -- Mail to my list address MUST be sent via the mailing list. All other mail to my list address will bounce. -- 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] Changing boolean to a smallint
Oops; see correction below: On 2010-11-04 16:41, Dean Gibson (DB Administrator) wrote: On 2010-11-04 15:41, Christine Penner wrote: I have a table column I want to change from a boolean to a smallint. changing false to 0 and true to 1. How do I do that? Christine Penner Ingenious Software 250-352-9495 ch...@fp2.ca ALTER TABLE table_name ALTER col_name TYPE SMALLINT USING CASE WHEN col_name THEN 1 ELSE 0 END; -- Mail to my list address MUST be sent via the mailing list. All other mail to my list address will bounce. -- 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] Linux
The choice depends more on what you want / need to have than what people think you want / need. If your corporation requires a support agreement, go either with Red Hat or with SuSE (Novell). If possible, have at least one of each of the above for a while -- one or two years -- and see what is better in your environment. I am more prone to use SuSE (SLES) as I have OpenSuSE on my laptop for years now. -- Jorge Godoy jgo...@gmail.com On Thu, Nov 4, 2010 at 13:00, Michael Gould mgo...@intermodalsoftwaresolutions.net wrote: I know that this is probably a religion issue but we are looking to move Postgres to a Linux server. We currently have a Windows 2008 R2 active directory and all of the other servers are virtualized via VMWare ESXi. One of the reasons is that we want to use a 64 bit Postgres server and the UUID processing contrib module does not provide a 64 bit version for Windows. I would also assume that the database when properly tuned will probably run faster in a *inx environment. What and why should I look at certain distributions? It appears from what I read, Ubanta is a good desktop but not a server. Best Regards -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax
[GENERAL] select to_char(current_timestamp, 'YYYY-WW');
Hello, sorry for the stupid question, but why has the week number changed from 44 to 45 this night? It is Friday, 2010-11-05 01:10, but I get now: pref= SELECT to_char(current_timestamp, '-WW'); to_char - 2010-45 (1 row) pref= SELECT CURRENT_DATE; date 2010-11-05 (1 row) pref= SELECT CURRENT_TIME; timetz --- 01:12:00.65546+01 (1 row) # date Fri Nov 5 01:13:57 CET 2010 # cat /etc/*release CentOS release 5.5 (Final) # rpm -qa|grep -i postgres compat-postgresql-libs-4-1PGDG.rhel5 postgresql-libs-8.4.5-1PGDG.rhel5 compat-postgresql-libs-4-1PGDG.rhel5 postgresql-docs-8.4.5-1PGDG.rhel5 postgresql-8.4.5-1PGDG.rhel5 postgresql-libs-8.4.5-1PGDG.rhel5 Regards Alex -- 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] select to_char(current_timestamp, 'YYYY-WW');
Alexander Farber alexander.far...@gmail.com writes: sorry for the stupid question, but why has the week number changed from 44 to 45 this night? WW is defined as starting the first week on the first day of the year. 2010 started on a Friday so the week number increments on Fridays. There are some other format codes with different behavior ... 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
[GENERAL] How do you control IMMUTABLE PG PROC results?
We have procs that would benefit from returning IMMUTABLE results. The procs are dependent on external tables that rarely change, but when they DO change, it would be great if we could expire the cache that the procs read from so that the procs are forced to re-evaluate the results. Is this possible? How do I know how many possible results are cached? -- 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] select to_char(current_timestamp, 'YYYY-WW');
On Fri, Nov 5, 2010 at 1:21 AM, Tom Lane t...@sss.pgh.pa.us wrote: WW is defined as starting the first week on the first day of the year. 2010 started on a Friday so the week number increments on Fridays. There are some other format codes with different behavior ... Thank you, that is what I thought But is there a format code for a week starting on Sunday or Monday? Sorry, I can't find it at http://www.postgresql.org/docs/8.4/interactive/functions-formatting.html Regards Alex -- 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] select to_char(current_timestamp, 'YYYY-WW');
I will try -IW On Fri, Nov 5, 2010 at 1:28 AM, Alexander Farber alexander.far...@gmail.com wrote: But is there a format code for a week starting on Sunday or Monday? Sorry, I can't find it at http://www.postgresql.org/docs/8.4/interactive/functions-formatting.html -- 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 do you control IMMUTABLE PG PROC results?
On Friday 5. November 2010 01.24.14 Carlo Stonebanks wrote: We have procs that would benefit from returning IMMUTABLE results. The procs are dependent on external tables that rarely change, but when they DO change, it would be great if we could expire the cache that the procs read from so that the procs are forced to re-evaluate the results. A function declared as IMMUTABLE can't, by definition, do database lookups. Then it has to be declared as STABLE. http://www.postgresql.org/docs/8.4/static/xfunc-volatility.html «An IMMUTABLE function cannot modify the database and is guaranteed to return the same results given the same arguments forever.» regards, Leif B. Kristensen -- 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 do you control IMMUTABLE PG PROC results?
On Thu, Nov 04, 2010 at 08:24:14PM -0400, Carlo Stonebanks wrote: We have procs that would benefit from returning IMMUTABLE results. The procs are dependent on external tables that rarely change, but when they DO change, it would be great if we could expire the cache that the procs read from so that the procs are forced to re-evaluate the results. Is this possible? How do I know how many possible results are cached? in addition to what Leif responded, please note that immutable functions *do not* cache results. depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
