Re: [GENERAL] How to dump JUST procedures/funnctions?
Afaik no, you can make a schema-dump and extract the function declarations from the dump. Yeah, that's what I was doing. Bloody tedious. Thanks anyway! -- 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] Full Text Search: howto manage multiple languages ?
On Sun, 21 Mar 2010, C?dric MOULLET wrote: Hi, I have the following problem with the FTS: the database contains information in several languages. As I understand, the FTS requires to associate a language when ts_vector is created. Is there any way to make a kind of FTS doesn't needs to be asscociated with specific language ! FTS requires its configuration, which binds specific lexeme type with stack of dictionaries and this has nothing with language. international search, without having to associate a specific language to the ts_vector ? I must admit that it's quite ambiguous, but let's imagine that you have a worldwide address repository. In that case, you can find Rue, Street, Strasse etc... which have all a low significancy. This particular problem can be solved using synonym dictionary, for example. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Licence
On Sunday 21 March 2010 21.11:56 Lew wrote: In at least some jurisdictions, if one party to a contract writes the language without input or emendation from the other party, that allows the other party to impose any reasonable interpretation on the wording. IOW, ambiguity is resolved in favor of the party who had no choice in the wording. That would mean the licensee gets to determine what without fee means, not the licensor. A (copyright) license and a contract are two entirely different things. By using PostgreSQL you do not enter a contract with the authors (or any other copyright holder) but you make use of a license that grants you certain permissions. The essential difference to a contract is that if the license terms are not to your liking, you can always quit using it. With a contract (especially those where one party alone wrote it - basically most contracts a private person will ever have with a company such as a bank, telco, insurance company, ) you are usually bound and can't quit without compensation, which is why the law protects the weaker party that much. cheers -- vbi -- Cum tacent, clamant. When they are silent, they shout. -Cicero signature.asc Description: This is a digitally signed message part.
Re: [GENERAL] Restrict allowed database names?
On Sunday 21 March 2010 02.01:27 Scott Mead wrote: On Sat, Mar 20, 2010 at 5:24 PM, Adam Seering aseer...@mit.edu wrote: Hi, I'm trying to set up an internal general-purpose PostgreSQL server installation. I want most users with login access to the server to be able to create databases, but only with names that follow a specified naming convention (in particular, approximately is prefixed with the owner's username). A subset of administrative users can create users with any name. The goal is to let users create arbitrary databases, but to force them to get approval for names that someone else (or some other service) might conceivably want. Is there any way to enforce this within PostgreSQL? Maybe something like a trigger on CREATE DATABASE, if that's possible? Hmmm... nothing like that I'm afraid... But, you could possibly make a shell script to the 'createdb' executable that would force a name-style, but even then, for any user to be able to successfully run the command, they need database logon / create database privs, so if someone : cat `which createdb` and you had made a script, they'd see what you were up to. It may be a way to get started though. Extending this: have your users not have createdb permission and write this script as a suid program. cheers -- vbi --Scott M -- this email is protected by a digital signature: http://fortytwo.ch/gpg signature.asc Description: This is a digitally signed message part.
Re: [GENERAL] Full Text Search: howto manage multiple languages ?
Thanks for your answer, Could you point me to documentation regarding the configuration (dictionary creation, synonym creation) of FTS and how to do it ? Thanks, Cédric 2010/3/22 Oleg Bartunov o...@sai.msu.su On Sun, 21 Mar 2010, C?dric MOULLET wrote: Hi, I have the following problem with the FTS: the database contains information in several languages. As I understand, the FTS requires to associate a language when ts_vector is created. Is there any way to make a kind of FTS doesn't needs to be asscociated with specific language ! FTS requires its configuration, which binds specific lexeme type with stack of dictionaries and this has nothing with language. international search, without having to associate a specific language to the ts_vector ? I must admit that it's quite ambiguous, but let's imagine that you have a worldwide address repository. In that case, you can find Rue, Street, Strasse etc... which have all a low significancy. This particular problem can be solved using synonym dictionary, for example. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/http://www.sai.msu.su/%7Emegera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Welcome to my world: http://www.cedricmoullet.com/ My Linked In profile: http://www.linkedin.com/in/cedricmoullet Twitter: http://twitter.com/cedricmoullet
Re: [GENERAL] AIX postgresql error
Vikram Patil wrote: Thanks Laurence for Reply. I can actually connect to server using network. I am just trying to avoid this warning. Your solution for listen_address will work but I want to keep it as * . Somehow it doesn't complain on any other *nix Operation Systems. Probably IPv6 is set up correctly on the machines where you don't get an error. You can unconfigure IPv6 on the AIX, then PostgreSQL won't try to listen on IPv6 ports. Ask the system administrators of the machine for help. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Determining the OID of a certain type
Hi *, I wanted to ask, if there is a method to determine the oid of a certain type using the C backend interface? I need to get access to a specific type for an IAM and therefore i want to construct an IndexTuple using the specified methods. However, I need the OID of the specific type for it. I considered writing some auxiliary function that returns the OID of the type using plpgsql. This works fine. However, I thought about something that I can use directly in C (server side) to get the oid, or do I have to set up a query to the database? Querying is currently the way I go here. However, it does not feel so right, to return the OID for the type I want to use, using a query. I hope, that there is some predefined function that I haven't found yet to determine the OID of some type (or relation etc). Best regards, Carsten Kropf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to dump JUST procedures/funnctions?
Carlo Stonebanks stonec.regis...@sympatico.ca writes: Afaik no, you can make a schema-dump and extract the function declarations from the dump. Yeah, that's what I was doing. Bloody tedious. Thanks anyway! It seems like it could be automated. pg_dump -Fc -s mydb mydb.dump pg_restore -l mydb.dump | grep FUNCTION mydb.list pg_restore -L mydb.list mydb.dump functionsonly.txt (or something more or less like that --- too early in the morning...) 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] Determining the OID of a certain type
Carsten Kropf ckro...@fh-hof.de writes: I wanted to ask, if there is a method to determine the oid of a certain type using the C backend interface? Starting from what? For built-in types it's usual practice to use the #define from pg_type.h, if there is one. If you have a string name for the type, there's a parser function to derive an OID, which I forget the name of but it's probably in parse_type.c. 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] Daylight savings time confusion
Tom, You said, It seems to me that you're not entirely understanding how timestamps work in Postgres. That is an understatement! Thank you very much for your explanation. I have forwarded it to the other members of my development group, with my suggestion that we follow your ideas for future projects. I am not sure how easy it will be to retrofit existing projects, but I am sure it should be done. One question: We have customers all over the world. It would be best if we could rely on the operating system (usually Windows Server 2003) to tell us what time zone we're in, rather than asking for a specific timezone when we want to know a wallclock time. Is that possible? If not, it's not that big a deal because our database includes a table named system_info that contains a single record describing the customer's environment. We could just add a timezone field to that table. But how would we do that? What data type should that column have, and what would a query look like that converts a time from UTC to local time based on that field? As I was typing that question, I think I came up with the answer: the question is irrelevant. The reason for having a field to store times in UTC is so that intervals between times can be calculated without worrying about daylight savings time. But Postgres will take the timezone into account when calculating intervals, so there is no reason at all to store a UTC version of the time. And, as you pointed out, storing the same value twice is horrible database design. RobR -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Holger Kalbas
http://pacakm.w.interia.pl/eric.html _ The New Busy is not the old busy. Search, chat and e-mail from your inbox. http://www.windowslive.com/campaign/thenewbusy?ocid=PID27925::T:WLMTAGL:ON:WL:en-US:WM_HMP:032010_3
[GENERAL] Reducing excess files in pg_xlog
Hi, I noticed there's 66 files in my pg_xlog directory. I changed my checkpoint_segments from 32 to 3 as I noticed it was too high, restarted PosgreSQL, but there are still 66 files in that directory and they're taking up about 1.1G. How can I get this list of files down? Thanks Thom
Re: [GENERAL] Reducing excess files in pg_xlog
Thom Brown thombr...@gmail.com writes: I noticed there's 66 files in my pg_xlog directory. I changed my checkpoint_segments from 32 to 3 as I noticed it was too high, restarted PosgreSQL, but there are still 66 files in that directory and they're taking up about 1.1G. How can I get this list of files down? Force a checkpoint, if one hasn't happened already. 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] Warm Standby Setup Documentation
I have looked all over but could not find any detailed docs on setting up a warm standby solution using PostgreSQL 8.4. I do know of http://www.postgresql.org/docs/8.4/static/warm-standby.html but was wondering if there was a more detailed document on this topic. Are people using this as a viable backup/hot spare solution? How has it worked out? Thank you Ogden -- 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] Reducing excess files in pg_xlog
On 22 March 2010 14:19, Tom Lane t...@sss.pgh.pa.us wrote: Thom Brown thombr...@gmail.com writes: I noticed there's 66 files in my pg_xlog directory. I changed my checkpoint_segments from 32 to 3 as I noticed it was too high, restarted PosgreSQL, but there are still 66 files in that directory and they're taking up about 1.1G. How can I get this list of files down? Force a checkpoint, if one hasn't happened already. regards, tom lane Hi Tom, Yeah, I've run a CHECKPOINT too, but no joy. Still the same number of files. Thom
Re: [GENERAL] Reducing excess files in pg_xlog
Thom Brown thombr...@gmail.com writes: On 22 March 2010 14:19, Tom Lane t...@sss.pgh.pa.us wrote: Force a checkpoint, if one hasn't happened already. Yeah, I've run a CHECKPOINT too, but no joy. Still the same number of files. Hm, it works for me. What PG version is this exactly? Are you sure the active value of checkpoint_segments really changed? (Use SHOW) 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] Reducing excess files in pg_xlog
On 22 March 2010 14:29, Tom Lane t...@sss.pgh.pa.us wrote: Thom Brown thombr...@gmail.com writes: On 22 March 2010 14:19, Tom Lane t...@sss.pgh.pa.us wrote: Force a checkpoint, if one hasn't happened already. Yeah, I've run a CHECKPOINT too, but no joy. Still the same number of files. Hm, it works for me. What PG version is this exactly? Are you sure the active value of checkpoint_segments really changed? (Use SHOW) regards, tom lane This is version 8.4.3 postgres=# show checkpoint_segments; checkpoint_segments - 3 (1 row) Not sure if it's of any relevance, but the file name sequence seems to go a bit weird. Nov 25 19:49 000100FA Nov 26 17:25 000100FB Dec 16 14:50 000100FC Dec 17 04:00 000100FD Dec 17 15:26 000100FE Dec 17 15:27 00010001 Dec 17 15:29 000100010001 Mar 22 14:28 000100BF And yes, this particular cluster has very low usage at the moment. Thom
Re: [GENERAL] Determining the OID of a certain type
Thanks a lot, basically I looked inside these functions in parse_type.c and did not find an easy to use application here. So I considered trying to construct the required arguments passed to typenameType. However, during the development, I found the following function: Oid TypenameGetTypid(const char *typname) which does indeed do the lookup of my particular type in the cache and system catalog tables. Thanks a lot for the hint! Best regards Carsten Kropf Am 22.03.2010 um 13:18 schrieb Tom Lane: Carsten Kropf ckro...@fh-hof.de writes: I wanted to ask, if there is a method to determine the oid of a certain type using the C backend interface? Starting from what? For built-in types it's usual practice to use the #define from pg_type.h, if there is one. If you have a string name for the type, there's a parser function to derive an OID, which I forget the name of but it's probably in parse_type.c. 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] Daylight savings time confusion
On 22 Mar 2010, at 14:08, Rob Richardson wrote: One question: We have customers all over the world. It would be best if we could rely on the operating system (usually Windows Server 2003) to tell us what time zone we're in, rather than asking for a specific timezone when we want to know a wallclock time. Is that possible? If Usually that timezone is set in the client program that connects to the database. If that program lives on a central location instead of at your customers' then you may be able to determine their timezone from the client they are using upstream and pass it along to the database server. For example, web browsers often pass along what timezone they're connecting from, so you may be able to set the client timezone based on that information. A drawback of storing a clients' timezone at the server is that you would be wrong if they are connecting from another location than they usually do, for example while at a conference in a different country. If you leave determining the timezone up to them you can't ever be wrong ;) Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4ba789e510411783369698! -- 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] Determining the OID of a certain type
Carsten Kropf ckro...@fh-hof.de writes: basically I looked inside these functions in parse_type.c and did not find an easy to use application here. So I considered trying to construct the required arguments passed to typenameType. However, during the development, I found the following function: Oid TypenameGetTypid(const char *typname) which does indeed do the lookup of my particular type in the cache and system catalog tables. Thanks a lot for the hint! Actually, parseTypeString() is what I was thinking of. TypenameGetTypid won't cope with schema-qualified names, nor a lot of other cases. 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] pgreplay log file replayer released
On Wed, Mar 17, 2010 at 2:06 PM, Albe Laurenz laurenz.a...@wien.gv.at wrote: I announce the first release of pgreplay, version 0.9.0 (Beta). Project home page: http://pgreplay.projects.postgresql.org/ pgreplay reads a PostgreSQL log file (*not* a WAL file), extracts the SQL statements and executes them in the same order and relative time against a PostgreSQL database cluster. Do you have a multi-threaded model that tracks which transactions each query belonged to and runs them concurrently like they were in the original setup? That's what I've been looking for. -- greg -- 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] Determining the OID of a certain type
Oh, thanks, that's right, I see that this function I used before, searches only in the currently used schema. The usage of parseTypeString is comparable easy, as well, so based on your hints, I will probably use this function. Best regards Carsten Kropf Am 22.03.2010 um 16:23 schrieb Tom Lane: Carsten Kropf ckro...@fh-hof.de writes: basically I looked inside these functions in parse_type.c and did not find an easy to use application here. So I considered trying to construct the required arguments passed to typenameType. However, during the development, I found the following function: Oid TypenameGetTypid(const char *typname) which does indeed do the lookup of my particular type in the cache and system catalog tables. Thanks a lot for the hint! Actually, parseTypeString() is what I was thinking of. TypenameGetTypid won't cope with schema-qualified names, nor a lot of other cases. 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 -- 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] stopping processes, preventing connections
Scott Marlowe scott.marl...@gmail.com writes: It was a few posts back, but our discussion point was minor point upgrades and the fact that OP was running 8.3.1 and not sure there were updates to 8.3.9 (or latest) out there for debian. I'm quite sure debian has 8.3.9 out by now. Yes: http://packages.debian.org/lenny/postgresql-8.3 http://packages.debian.org/etch-backports/postgresql-8.3 You wont' find it in testing/unstable though, because next stable will contain 8.4 only, as far as I understand. Regards, -- dim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Reducing excess files in pg_xlog
Thom Brown wrote: I noticed there's 66 files in my pg_xlog directory. I changed my checkpoint_segments from 32 to 3 as I noticed it was too high, restarted PosgreSQL, but there are still 66 files in that directory and they're taking up about 1.1G. How can I get this list of files down? A checkpoint after there's been some write activity in the database should reduce this down to a reasonable number. If the system has been idle since the last checkpoint, it doesn't do anything when you ask for another one, which includes skipping this cleanup; that may be why you haven't seen it drop yet. I'm assuming you don't have WAL shipping turned on by setting archive_command. There can also be an excess of these segments that can't be cleaned up if your archiving scheme fails. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] user variables in session
Hi All,
I wasn't able to find any functions which would allow me to save/fetch/remove
user variables local to pgsql session, e.g. -
1. select setvar('user variable name', 'user value');
2. select getvar('user variable name');
3. select delvar('user variable name');
Is there any way to do this with stock PostgreSQL, or via something in
contrib?
Would pgsql's HTAB with hash_create and hash_search be a good basis for a
simple C extension which would provide such feature ?
I found this to be quite useful in e.g. pl/pgsql triggers, for logging.
--
Best Regards,
Igor Shevchenko
--
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] user variables in session
On Mon, Mar 22, 2010 at 11:55 AM, Igor Shevchenko i...@carcass.ath.cx wrote:
Hi All,
I wasn't able to find any functions which would allow me to save/fetch/remove
user variables local to pgsql session, e.g. -
1. select setvar('user variable name', 'user value');
2. select getvar('user variable name');
3. select delvar('user variable name');
Is there any way to do this with stock PostgreSQL, or via something in
contrib?
Would pgsql's HTAB with hash_create and hash_search be a good basis for a
simple C extension which would provide such feature ?
yes. this is trivially done and works. another way to do it with
pl/perl...there's an example in the docs iirc. personally, I prefer
the htab route. It's going to come down to which dependency you want
to deal with.
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] strange
Tom Lane wrote: Szymon Guz mabew...@gmail.com writes: I've got a simple query. When I use explain analyze it lasts 7 times slower. Why? You've got a machine where gettimeofday() is really slow. This is common on cheap PC hardware :-( I'd be curious to know more about the hardware and operating system Szymon is using if you suspect this is the case. I keep hearing about systems where this is slow, but despite claims that they're common I've never actually seen one. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us
[GENERAL] Avoiding deadlocks on mass delete / update
I've got a simple 'spool' table, one process 'worker' reads and updates this table, other 'stat' performs 'delete ... where ... returning *'. Sometimes I've got dedlocks on delete operation in 'stat', seems like at the moment of expiration of data by timeout some state changes arrived from worker. So the question, is it possible to somehow set order of row deletion in such bulk delete operation, to avoid deadlocks? Thank you beforehand -- MRJ -- 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] strange
Greg Smith g...@2ndquadrant.com writes: Tom Lane wrote: You've got a machine where gettimeofday() is really slow. This is common on cheap PC hardware :-( I'd be curious to know more about the hardware and operating system Szymon is using if you suspect this is the case. I keep hearing about systems where this is slow, but despite claims that they're common I've never actually seen one. Well, they're not as common as they used to be. My understanding is that there are two independent issues: * If you have to call into the kernel to read the RTC, you're already hurting. Modern Unixen avoid this, but I think I've read that it's generally only fixed on x86_64 hardware not i386. * The original specs for reading the RTC on PC hardware did not foresee the desire of being able to read it out in a small fraction of a microsecond. I don't know the details on this exactly, but some googling turned up this: http://linux.derkeiler.com/Mailing-Lists/Kernel/2006-07/msg07415.html The OP's example involved almost 21 seconds added by approximately 2*1000 gettimeofday probes, or right about 1 microsecond per probe... 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] Reducing excess files in pg_xlog
On 22 March 2010 16:06, Greg Smith g...@2ndquadrant.com wrote: Thom Brown wrote: I noticed there's 66 files in my pg_xlog directory. I changed my checkpoint_segments from 32 to 3 as I noticed it was too high, restarted PosgreSQL, but there are still 66 files in that directory and they're taking up about 1.1G. How can I get this list of files down? A checkpoint after there's been some write activity in the database should reduce this down to a reasonable number. If the system has been idle since the last checkpoint, it doesn't do anything when you ask for another one, which includes skipping this cleanup; that may be why you haven't seen it drop yet. I'm assuming you don't have WAL shipping turned on by setting archive_command. There can also be an excess of these segments that can't be cleaned up if your archiving scheme fails. Hi Greg, You're right, I don't have the archiving enabled. All options are commented out. I've just forced a new WAL file by writing lots of stuff to a table, then deleting it. It appears to be recyling the filenames now. The latest files end in DD and DE, and the earliest is DF. Presumably these will slowly get eaten up until it's just down to the 3 files I've configured it for? Thom
Re: [GENERAL] Reducing excess files in pg_xlog
Thom Brown thombr...@gmail.com writes: I've just forced a new WAL file by writing lots of stuff to a table, then deleting it. It appears to be recyling the filenames now. The latest files end in DD and DE, and the earliest is DF. Presumably these will slowly get eaten up until it's just down to the 3 files I've configured it for? The expected steady state is something like 2*checkpoint_segments+1 files. See docs. 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] Create a function that updates the record with and timestamps
I would like to have postgres update the last_modified column with the current_date on an update of the record. I am not sure if there is a very simple way of doing this? Or, do I need to create a function and a trigger to call the row and update with new data and set the last_modified to current_date? Here is the table. CREATE TABLE price.price_table ( PRICE_DATE DATE, ID VARCHAR(13), OPENING NUMERIC(18,6), CLOSING NUMERIC(18,6), HIGHEST NUMERIC(18,6), LOWEST NUMERIC(18,6), VOLUME BIGINT, LAST_MODIFIED TIMESTAMP(6) WITHOUT TIME ZONE DEFAULT current_date, CONSTRAINT PK_PRICE PRIMARY KEY (PRICE_DATE,ID)); Any help would be appreciated. Cheers, Chris _ Take your contacts everywhere http://go.microsoft.com/?linkid=9712959
Re: [GENERAL] strange
2010/3/22 Greg Smith g...@2ndquadrant.com Tom Lane wrote: Szymon Guz mabew...@gmail.com mabew...@gmail.com writes: I've got a simple query. When I use explain analyze it lasts 7 times slower. Why? You've got a machine where gettimeofday() is really slow. This is common on cheap PC hardware :-( I'd be curious to know more about the hardware and operating system Szymon is using if you suspect this is the case. I keep hearing about systems where this is slow, but despite claims that they're common I've never actually seen one. Hi, the laptop that I use right now is Compaq 6710b, 4GB RAM, Ubuntu 64bit, kernel from distribution, hdd is new szy...@ymon:~$ cat /proc/version Linux version 2.6.31-20-generic (bui...@crested) (gcc version 4.4.1 (Ubuntu 4.4.1-4ubuntu8) ) #58-Ubuntu SMP Fri Mar 12 04:38:19 UTC 2010 regards Szymon
Re: [GENERAL] Create a function that updates the record with and timestamps
In response to Chris Barnes : I would like to have postgres update the last_modified column with the current_date on an update of the record. I am not sure if there is a very simple way of doing this? Or, do I need to create a function and a trigger to call the row and update with new data and set the last_modified to current_date? Yes, that's the way, a TRIGGER on UPDATE for each row. I think, the doc contains an example. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgresql error from interface
Hi I have an interface (Delphi) for my Postgresql database, that is telling me that I have an inconsistancy between one of the Postgresql triggers and a Postgresql table. The problem for me is - the message doesn't give me enough information to determine which trigger has the error. Is there some way of doing an overall search without having to search each trigger individually?? Bob
Re: [GENERAL] Postgresql error from interface
Bob Pawley rjpaw...@shaw.ca writes: I have an interface (Delphi) for my Postgresql database, that is telling me that I have an inconsistancy between one of the Postgresql triggers and a Postgresql table. The problem for me is - the message doesn't give me enough information to determine which trigger has the error. Is there some way of doing an overall search without having to search each trigger individually?? You could try looking in the postmaster log to see if there's any more information in the underlying Postgres error message(s). 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] Create a function that updates the record with and timestamps
I see examples for updating tables using a function, but I would like to pull the row requested and modify the last_modified column with current_date and push the modified data back into the same row. I did see an example of how to use old and new at this at this link, but it is vague. http://www.faqs.org/docs/ppbook/x20655.htm#TRIGGERFUNCTIONVARIABLES From: compuguruchrisbar...@hotmail.com To: pgsql-general@postgresql.org Subject: [GENERAL] Create a function that updates the record with and timestamps Date: Mon, 22 Mar 2010 12:58:49 -0400 I would like to have postgres update the last_modified column with the current_date on an update of the record. I am not sure if there is a very simple way of doing this? Or, do I need to create a function and a trigger to call the row and update with new data and set the last_modified to current_date? Here is the table. CREATE TABLE price.price_table ( PRICE_DATE DATE, ID VARCHAR(13), OPENING NUMERIC(18,6), CLOSING NUMERIC(18,6), HIGHEST NUMERIC(18,6), LOWEST NUMERIC(18,6), VOLUME BIGINT, LAST_MODIFIED TIMESTAMP(6) WITHOUT TIME ZONE DEFAULT current_date, CONSTRAINT PK_PRICE PRIMARY KEY (PRICE_DATE,ID)); Any help would be appreciated. Cheers, Chris Date: Mon, 22 Mar 2010 18:14:00 +0100 From: andreas.kretsch...@schollglas.com To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Create a function that updates the record with and timestamps In response to Chris Barnes : I would like to have postgres update the last_modified column with the current_date on an update of the record. I am not sure if there is a very simple way of doing this? Or, do I need to create a function and a trigger to call the row and update with new data and set the last_modified to current_date? Yes, that's the way, a TRIGGER on UPDATE for each row. I think, the doc contains an example. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general _ IM on the go with Messenger on your phone http://go.microsoft.com/?linkid=9712960
Re: [GENERAL] pgreplay log file replayer released
Greg Stark gsst...@mit.edu writes: Do you have a multi-threaded model that tracks which transactions each query belonged to and runs them concurrently like they were in the original setup? That's what I've been looking for. Tsung does that and has been doing it for… quite some time. It even comes with a recorder which is a PostgreSQL proxy: connect it to your server, connect your client to it, and let it record a session at a time. Then in the configuration you get to choose how many of each session you want to mix, etc. http://tsung.erlang-projects.org/ Regards, -- dim My TODO has write a Tsung blog entry (series?) and a tutorial, but you'll have to wait until after extensions and some other things, or do it yourself... sorry about that... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] server-side extension in c++
Hi All, Is there an easy way to add c++ files to my simple pgsql module ? My Makefile is as follows - === MODULES = pg_uservars DATA_built = pg_uservars.sql PGXS := $(shell pg_config --pgxs) include $(PGXS) === I've got pg_uservars.c and hv.cc and I'd like to compile hv.cc via g++. I'm aware of c++ name [de]mangling, just looking if there's a standard way of using C++ when it comes to pgxs. -- Best Regards, Igor Shevchenko -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to dump JUST procedures/funnctions?
On Sat, Mar 20, 2010 at 11:09 PM, Carlo Stonebanks stonec.regis...@sympatico.ca wrote: Is pg_get_functiondef an 8.4 appears to be an 8.4 function? I don't see it in the 8.3 documentation and the servers in question are all 8.3. Any alternatives for 8.3? pg_proc has the code body, but not the function declaration, etc. I've attached a simple perl script to dump all functions per schema from a database. I won't claim this is perfect, but it does the trick for me. Works with 8.3. If you have 2 functions called public.foo, it will put them in public/foo.sql with CREATE OR REPLACE FUNCTION formatting around it. Hope this is useful to others. Tony Wasson dump-all-functions.pl Description: Binary data -- 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] Create a function that updates the record with and timestamps
On Monday 22 March 2010 10:55:36 am Chris Barnes wrote: I see examples for updating tables using a function, but I would like to pull the row requested and modify the last_modified column with current_date and push the modified data back into the same row. I did see an example of how to use old and new at this at this link, but it is vague. http://www.faqs.org/docs/ppbook/x20655.htm#TRIGGERFUNCTIONVARIABLES Something like this: CREATE OR REPLACE FUNCTION public.ts_update() RETURNS trigger AS $Body$ BEGIN NEW.ts_update:=timeofday(); RETURN NEW; END; $Body$ LANGUAGE 'plpgsql'; -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] string functions and operators
Hello,
I have a dilema and I was hoping someone here may offer guidance or assistance.
I bet this is a very simple question for someone out there but I am having
problems coming up with a solution. Here it is...
suppose I have a field with the following values:
77.1
77.2
134.1
134.2
134.3
5.1
5.2
I need two seperate SELECT queries. One would return the following values
(everything left of the decimal point)
77
77
134
134
5
5
The second query would return all of the values to the right of the decimal
point:
1
2
1
2
3
1
2
Now, I have been using the following information (although very Greek) to try
to solve this problem:
http://www.postgresql.org/docs/current/static/functions-string.html
And I have been playing around with the syntax of the following:
substring('112.5' from '%#___.#_' for '#')
but the aforementioned is not quite working out... can someone please show me a
string function that will produce the desired results?
Thanks!
~n
Re: [GENERAL] string functions and operators
Neil Stlyz wrote: Hello, I have a dilema and I was hoping someone here may offer guidance or assistance. I bet this is a very simple question for someone out there but I am having problems coming up with a solution. Here it is... suppose I have a field with the following values: 77.1 77.2 134.1 134.2 134.3 5.1 5.2 I need two seperate SELECT queries. One would return the following values (everything left of the decimal point) 77 77 134 134 5 5 The second query would return all of the values to the right of the decimal point: 1 2 1 2 3 1 2 silly me says.. SELECT FLOOR(x), x-FLOOR(x) FROM TABLE; at least for numeric values. but, in string space, u... select split_part(x,'.',1), split_part(x,'.',2) from table; or select regexp_replace(x, '\.[0-9]*$',''), regexp_replace(x,'^[0-9]*\.','') from table; or god knows how many others. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Replace null values
Hi, In my query, some rows have null values (length 0). I wish to replace them with some constant. I think I am wrong somewhere in this query using coalesce(): select coalesce(u.name, 'anon'), nodecount from users u, ( select n.uid userid, count(n.nid) nodecount from node n group by n.uid order by n.uid ) t1 where u.uid = t1.userid order by nodecount; The output is same as that of without coalesce. -- Nilesh Govindarajan Site Server Administrator www.itech7.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] Replace null values
Nilesh Govindarajan wrote: Hi, In my query, some rows have null values (length 0). a NULL value is not length 0, NULL is not the empty string, rather, NULL is no value at all. if you want to change a 0 length string to something, use a CASE or something. select CASE WHEN u.name = '' THEN 'anon' ELSE u.name, -- 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] Replace null values
On 03/23/2010 10:07 AM, John R Pierce wrote: Nilesh Govindarajan wrote: Hi, In my query, some rows have null values (length 0). a NULL value is not length 0, NULL is not the empty string, rather, NULL is no value at all. if you want to change a 0 length string to something, use a CASE or something. select CASE WHEN u.name = '' THEN 'anon' ELSE u.name, Thanks a lot ! It worked :) -- Nilesh Govindarajan Site Server Administrator www.itech7.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] Replace null values
On 03/23/2010 09:47 AM, Osvaldo Kussama wrote:
2010/3/23 Nilesh Govindarajanli...@itech7.com:
Hi,
In my query, some rows have null values (length 0).
NULL or a zero lenght string?
I wish to replace them with some constant.
I think I am wrong somewhere in this query using coalesce():
select coalesce(u.name, 'anon'), nodecount from users u, ( select n.uid
userid, count(n.nid) nodecount from node n group by n.uid order by n.uid )
t1 where u.uid = t1.userid order by nodecount;
The output is same as that of without coalesce.
bdteste=# SELECT coalesce(NULL, 'anon'), coalesce('', 'anon');
coalesce | coalesce
--+--
anon |
(1 registro)
Osvaldo
It is a zero length string. Somebody on the list suggested to use CASE.
It worked. Thanks anyways.
Got to learn about coalesce that it replaces null values and not zero
length strings.
How to convert zero length string to null ?
--
Nilesh Govindarajan
Site Server Administrator
www.itech7.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] Create a function that updates the record with and timestamps
In response to Richard Sickler : I am not sure if there is a very simple way of doing this? Or, do I need to create a function and a trigger to call the row and update with new data and set the last_modified to current_date? Yes, that's the way, a TRIGGER on UPDATE for each row. I think, the doc contains an example. Andreas From a novice: I use last_updated_at timestamp without time zone NOT NULL DEFAULT now() This works only for INSERT, but not for UPDATE. test=# create table richard (id int, last_updated_at timestamp without time zone NOT NULL DEFAULT NOW()); CREATE TABLE test=# insert into richard (id) values (1); INSERT 0 1 test=# select * from richard ; id | last_updated_at + 1 | 2010-03-23 06:54:28.656668 (1 row) test=# select now(); now --- 2010-03-23 06:54:42.443224+01 (1 row) test=# UPDATE richard set id=2 where id=1; UPDATE 1 test=# select * from richard ; id | last_updated_at + 2 | 2010-03-23 06:54:28.656668 (1 row) As you can see, the last_updated_at isn't up-to-date ;-) Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
