Re: [GENERAL] PG Email Client
Tom Allison wrote: I would be careful about using dspam. I have documented cases where it lost email and even with posting all the debug and other logs on the mailing lists I've never received any indication that they recognize this as a dspam problem. dspam will lose your mail. This is getting off topic, but I can't just let this hang in the air. I know there have been cases - particularly in old versions ( 1 year old IIRC) where dspam would fail to deliver a message w/o returning an error code. I'm sure there are combinations of MTA - dspam - delivery agent that break. Dspam can be used in so many ways, it's very difficult to cover every possible scenario it can be deployed in. However, anything remotely recent (I still use an old 3.6.6) returns an error code on failure. For the rest, it is up to your MTA to respond to those error codes, crashes of dspam, etc. I use postfix, and it does check things like that. As a matter of fact, I've had trouble in the past where postfix decided dspam wasn't going to finish processing mail and delivered the message regardless (that was when my mail/spam machine was a P233 w/ only 64MB RAM - it simply couldn't cope). I know there are bugs in dspam, but I doubt it's losing mail. That said, anything beyond the scope of its performance on postgres is off topic and should be taken off list. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] ff
-- Confidentiality Notice: The information contained in this e-mail and any accompanying attachment(s) is intended only for the use of the intended recipient and may be confidential and/or privileged of Neusoft Group Ltd., its subsidiaries and/or its affiliates. If any reader of this communication is not the intended recipient, unauthorized use, forwarding, printing, storing, disclosure or copying is strictly prohibited, and may be unlawful. If you have received this communication in error, please immediately notify the sender by return e-mail, and delete the original message and all copies from your system. Thank you. --- attachment: Nature_Bkgrd.jpg
Re: [GENERAL] partitioning / rules - strange behavior
Suddenly stops working, or continues doing exactly what it did before?
I'm wondering if you are relying on a cached plan that doesn't include
the new rule.
regards, tom lane
If there´s only the insert_850 RULE then everything works as expected - the
insert prints INSERT 0 0, the row is inserted into the correct partition
which is sessions_850 - I can fetch it using either
SELECT * FROM sessions WHERE id = currval('sessions_id_seq');
or direcly by
SELECT * FROM sessions_850 WHERE id = currval('sessions_id_seq');
When I create the next next rule (insert_900 for ids between 900 and
949) it stops working - it prints INSERT 0 0 just as before, everything
seems fine, but the row disappears - it's not available .
I'm not sure about the query plans, but I think I've checked that and everything
seemed ok - all the partitions were used as far as I remember. But this
shouldn't be a problem as we have not reached the 900 limit yet (so the new
partition is not used at all). And we've tried to restart the PostgreSQL as the
last hope, yesterday, so there really should be no old plans.
I don't have an access to the production database (I have not been able to
simulate this on the development/testing system) - I'll play with that at night
(european time). I'll try to drop / recreate the partition (I've tried to
recreate only the RULEs, not the partitions).
Tomas
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] 8.2.1 Compiling Error
On Tue, Jan 30, 2007 at 03:05:27PM -0800, elein wrote: Debian Linux. Have always built from scratch with no problem. This is 8.2.1 from postgresql.org. snip gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wendif-labels -fno-strict-aliasing -g -Wno-error -L../../../../src/port -Wl,-rpath,'/local/pgsql82/lib' preproc.o type.o ecpg.o ecpg_keywords.o output.o keywords.o c_keywords.o ../ecpglib/typename.o descriptor.o variable.o -lpgport -lz -lreadline -lcrypt -ldl -lm -o ecpg /usr/lib/libc_nonshared.a(elf-init.oS)(.gnu.linkonce.t.__i686.get_pc_thunk.bx+0x0): In function `__i686.get_pc_thunk.bx': : multiple definition of `__i686.get_pc_thunk.bx' The only place I've seen this error is when there is a version mismatch in the use of the compiler. I had it on a system with several versions of gcc/g++ installed and some varients of binutils. Once I pruned out the versions I didn't actually need and did a make clean, the problem went away. Hope this helps, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] Some issues with planner and query optimization
Boguk Maxim wrote: Postgres 8.1 Sample test table: (all queries done on fresh vacuumed analyzed table with statistics on rub_id and news_dtime set to 1000) (all table in memory and server do not doing anything other) media= \d test_table Table public.test_table Column |Type | Modifiers +-+--- id | integer | rub_id | integer | news_id| integer | news_dtime | timestamp without time zone | Indexes: test_table_pk UNIQUE, btree (id) test_table_main_idx btree (rub_id, news_dtime) media= select count(*) from test_table; count - 5834463 media= select count(distinct rub_id) from test_table; count --- 342 Now doing 3 simple query: First: media= EXPLAIN ANALYZE select * from test_table where rub_id IN (5) order by news_dtime limit 20; QUERY PLAN Limit (cost=0.00..10.73 rows=20 width=20) (actual time=0.018..0.121 rows=20 loops=1) - Index Scan using test_table_main_idx on test_table (cost=0.00..29758.11 rows=55447 width=20) (actual time=0.014..0.054 rows=20 loops=1) Index Cond: (rub_id = 5) Total runtime: 0.186 ms Second (almost same but with rub_id 8): media= EXPLAIN ANALYZE select * from test_table where rub_id IN (8) order by news_dtime limit 20; QUERY PLAN - Limit (cost=0.00..1.98 rows=20 width=20) (actual time=0.019..0.121 rows=20 loops=1) - Index Scan using test_table_main_idx on test_table (cost=0.00..45976.37 rows=463684 width=20) (actual time=0.014..0.054 rows=20 loops=1) Index Cond: (rub_id = 8) Total runtime: 0.186 ms Now try with rub_id IN (5,8) (I was assumed query will work 2-10 time longer max... With almost same plan) But i'm got bad plan/really slow query: media= EXPLAIN ANALYZE select * from test_table where rub_id IN (5,8) order by news_dtime limit 20; QUERY PLAN - Limit (cost=103337.45..103337.50 rows=20 width=20) (actual time=4437.841..4437.976 rows=20 loops=1) - Sort (cost=103337.45..104624.26 rows=514725 width=20) (actual time=4437.836..4437.873 rows=20 loops=1) Sort Key: news_dtime - Bitmap Heap Scan on test_table (cost=3818.96..54506.92 rows=514725 width=20) (actual time=82.139..1100.021 rows=515340 loops=1) Recheck Cond: ((rub_id = 5) OR (rub_id = 8)) - BitmapOr (cost=3818.96..3818.96 rows=519131 width=0) (actual time=80.498..80.498 rows=0 loops=1) - Bitmap Index Scan on test_table_main_idx (cost=0.00..409.06 rows=55447 width=0) (actual time=8.342..8.342 rows=54959 loops=1) Index Cond: (rub_id = 5) - Bitmap Index Scan on test_table_main_idx (cost=0.00..3409.89 rows=463684 width=0) (actual time=72.146..72.146 rows=460381 loops=1) Index Cond: (rub_id = 8) Total runtime: 4458.999 ms (11 rows) Ouch 25000 slower... Why planner not try two index scan and merge results... Try: ORDER BY rub_id, news_dtime Does that give it enough of a hint? The problem is you're asking for the 20 oldest regardless of rub_id, so the index isn't as much use as it might be. Perhaps an index on (news_dtime,rub_id) rather than the other way around? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Index bloat of 4x
Jim Nasby wrote:
Is there no way to change the index code to allow for moving index
tuples from one page to another? If we could do that then presumably
we could free up substantially more pages.
This paper
@inproceedings{DBLP:conf/sigmod/ZouS96,
author= {C. Zou and B. Salzberg},
editor= {H. V. Jagadish and Inderpal Singh Mumick},
title = {On-line Reorganization of Sparsely-populated B+trees},
booktitle = {Proceedings of the 1996 ACM SIGMOD International Conference on
Management of Data, Montreal, Quebec, Canada, June 4-6, 1996},
publisher = {ACM Press},
year = {1996},
pages = {115-124},
bibsource = {DBLP, \url{http://dblp.uni-trier.de}}
}
may be of some use here.
http://citeseer.ist.psu.edu/zou96line.html
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] database backup trouble
2007/2/3, George Weaver [EMAIL PROTECTED]: it's a windows-related problem Is the Task Scheduler service running? (Start Settings Control Panel Administrative Tools Services - Task Scheduler?). If the Task Scheduler service is running , what does the Task Scheduler log indicate about the tasks you tried to run (menu item: Advanced View Log)? Thanks for the tip: I'll check the log. I just did a test on the office W2k3 server and the test was successful in that the task ran. This leads me to believe it's a matter of priviledges: our client has much stricter user rights policies than we do at the office. :) Did any of your test .bat files contain a PAUSE command to keep the console window open in case the bat file did run as scheduled? As I said, it doesn't seem to be related to the script contents (see above) and I'm kind of surprised all the reactions on the mailing list seem to be of the windows-scheduled-tasks-don't-work?-really?-type instead of the pgagent-works-like-a-clock-you-just-have-to-wind-it-up type of anwer, as I had hoped and expected. this is the beanshell script: Unfortunately I am not familar with beanshell and cannot offer assistance here. These are just my very mediocre attempts to get around the fact that I can't get around pg_dump, i.e. there seems to be no way to issue a request from a client on the network and get the database dump from the server: I listed the beanshell approach to a platform independent backup solution only for completeness sake. Thanks, t.n.a. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] PostgreSQL/FireBird
FireBird has a different target audience than PostgreSQL, which is why it's not compared to PostgreSQL as often as MySQL is. FireBird is a direct descendant of Borland InterBase 6. Consequently, much like Postgres inherited a lot of Ingres's weirdness (most of which has since been weeded out or superceeded with standard SQL compliance), FireBird is still very much InterBase dialect-compliant. This is also why it still uses a modified Mozilla Public License. I know they've achieved ANSI SQL-92 compliance, but I don't know how fully compliant beyond that they are. PostgreSQL is mostly working on SQL-03 compliance AFAICT. Both use MVCC. Interbase was also primarily used for single instance and embedded applications, so it's not intended to scale the same way PostgreSQL is. Firebird's design foci are very small memory footprint, ANSI SQL-92 complaince, multiple dialects that support aging systems, and very low administrative requirements. It lack features and scalability compares to PG, but does what it does very well. PostgreSQL's design foci are features and robustness. It's designed to compete with Oracle, DB2, MS SQL, and other top-end enterprise databases. It has a much larger memory footprint and is much more complicated to administer compared to FB, but is much more configurable and customizable. Bottom line: PostgreSQL is more mature because it's several years older. Firebird is intended for different applications. -- Brandon Aiken CS/IT Systems Engineer -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of RPK Sent: Thursday, February 01, 2007 10:32 PM To: pgsql-general@postgresql.org Subject: [GENERAL] PostgreSQL/FireBird How is FireBird rated when compared with PostgreSQL? -- View this message in context: http://www.nabble.com/PostgreSQL-FireBird-tf3158857.html#a8761237 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ** LEGAL DISCLAIMER ** Statements made in this e-mail may or may not reflect the views and opinions of Wineman Technology, Inc. or its employees. This e-mail message and any attachments may contain legally privileged, confidential or proprietary information. If you are not the intended recipient(s), or the employee or agent responsible for delivery of this message to the intended recipient(s), you are hereby notified that any dissemination, distribution or copying of this e-mail message is strictly prohibited. If you have received this message in error, please immediately notify the sender and delete this e-mail message from your computer. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] ERROR: missing cache data for cache id 27
On Sun, Feb 04, 2007 at 23:43:48 -0800, David Fetter [EMAIL PROTECTED] wrote: On Sun, Feb 04, 2007 at 03:18:07PM -0200, Jorge Godoy wrote: Tom Lane [EMAIL PROTECTED] writes: Jorge Godoy [EMAIL PROTECTED] writes: I'm using PostgreSQL 8.1.4 and psql 8.1.4 as well. This was fixed in 8.1.5 ... or at least the only known cause was fixed. Thanks! I'll bug OpenSuSE guys to release an 8.1.5 package ;-) 8.1.6 is the current one. That is so yesterday. You'll want to get 8.1.7 now. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Production systems beware: U.S. Daylight Savings Time comes at a new time this year
Sorry if I'm the only one to find this amusing, but I see that the original message was sent twenty minutes =after= I received it. :) - John D. Burger MITRE ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] postgresql fails to start after symlinking pgdata dir
In the past I've always done the following with success (this is under fedora core 6 x86_64): mv /var/lib/pgsql/data /data/pg/ ln -s /data/pg/data /var/lib/pgsql/data /etc/init.d/postgresql start I just installed 8.2.2 on a new FC6 machine and when I do that it fails to start. The logs reveal nothing. Any suggestions? Any help would be appreciated. Thanks Gene
Re: [GENERAL] Can a function be parameter in PL/PGSQL function?
On 2/3/07, elein [EMAIL PROTECTED] wrote: On Tue, Jan 30, 2007 at 12:32:04PM -0800, Karen Hill wrote: Is it possible to have a pl/pgsql function take another pl/pgsql function as one of the parameters? I think that OP meant (correct me if I'm wrong!) to be able to do something like: create function bar(text) returns text as $$ select $1 || 'xyz'; $$ language sql; create function something_complex(regprocedure) returns void as [...] language plpgsql; select something_complex(bar(text)); ...so you pass the callback 'bar' to the complex function which executes it over something. This isn't possible currently, but you can fudge it with dynamic sql for simple things (no arrays, records, or cursors), or work up something more general solution with a C go-between. merlin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] New RPM Sets for Fedora Core / Red Hat Enterprise Linux
- PostgreSQL New RPM Sets 2007-02-05 Versions: 8.2.2, 8.1.7, 8.0.11, 7.4.16, 7.3.18 Set label: 8.2.2-1PGDG, 8.1.7-1PGDG, 8.0.11-1PGDG, 7.4.16-1PGDG, 7.3.18-1PGDG - - Release Info: PostgreSQL RPM Building Project has released RPMs for new PostgreSQL minor releases, and they are available in main FTP site and its mirrors. Users should upgrade to these versions as soon as possible. We currently have RPMs for: - Fedora Core 2-x86_64 - Fedora Core 4 - Fedora Core 5-x86_64 - Fedora Core 6 - Fedora Core 6-x86_64 - Red Hat Enterprise Linux Enterprise Server 4 - Red Hat Enterprise Linux Advanced Server 4 RPMs for the following platforms will be on main FTP site very shortly: - Fedora Core 5 - Red Hat Enterprise Linux Enterprise Server 3.0 - Red Hat Enterprise Linux Enterprise Server 4-x86_64 - Red Hat Enterprise Linux Advanced Server 4-x86_64 More may (will) come later. I want to thank every package builder for this great number of supported platforms. Support for Red Hat 9, RHEL 2.1 and Fedora Core 1 and 3 was already abandoned . Fedora Core 2 and Fedora Core 4 support will be abandoned in future releases. Please let us know if you can assist us in building RPMS of missing Red Hat / Fedora Core platforms. There is a new package layout as of PostgreSQL 8.2.2. All previous releases were including a single postgresql-pl package which included PL/Perl, PL/Python and PL/Tcl. Since the old layout made people install all dependencies for all languages, we splitted this package into 3 subpackage to have less dependencies: * postgresql-plperl * postgresql-plpython * postgresql-pltcl Either of these packages will obsolete postgresql-pl, so install whichever you want during the upgrade. 8.2.2 set contains a package that was introduced in 8.2.0: compat-postgresql-libs. Fedora Core 5, Fedora Core 6, Red Hat Enterprise Linux 4 and Red Hat Enterprise Linux 5 ship with libpq.so.4. Any apps that are built against libpq need this, like php-pgsql, However, since PostgreSQL 8.2 ships with libpq.so.5, users will need old libs in order not to break apps. For example, if you are running PHP+PostgreSQL 8.1.X on FC6 and if you want to upgrade to PostgreSQL 8.2, you will need this package before upgrading to 8.2. We are shipping libpq.so.4 with this new set. Please install compat-postgresql-libs before installing other packages. For complete list of changes in RPM sets, please refer to the changelogs in the RPMs. Use rpm -q -changelog package_name for querying the changelog. Since this release is not a major release, it will not requires a dump/reload from the previous release. However, if you are upgrading from very early releases, you may need to upgrade. Please see the Release Notes to confirm procedures for this. The SRPMs are also provided. Please note that we have one SRPM for all platforms. We also have a howto document about RPM installation of PostgreSQL: http://pgfoundry.org/docman/?group_id=148 Please follow the instructions before installing/upgrading. Almost each RPM has been signed by the builder, and each directory contains CURRENT_MAINTAINER file which includes the name/email of the package builder and link to their PGP key. If you experience problems with the RPMs or if you have feature requests, please join pgsqlrpms-general ( at ) pgfoundry ( dot ) org More info about the list is found at: http://lists.pgfoundry.org/mailman/listinfo/pgsqlrpms-general The project page is: http://pgfoundry.org/projects/pgsqlrpms Please do not use these resources for issue running or using PostgreSQL once it is installed. Please download these files from: http://www.postgresql.org/ftp/binary/v8.2.2/linux/ http://www.postgresql.org/ftp/binary/v8.1.7/linux/ http://www.postgresql.org/ftp/binary/v8.0.11/linux/ http://www.postgresql.org/ftp/binary/v7.4.16/linux/ -- Devrim GÜNDÜZ PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/ signature.asc Description: This is a digitally signed message part
Re: [GENERAL] postgresql fails to start after symlinking pgdata dir
Gene [EMAIL PROTECTED] writes: In the past I've always done the following with success (this is under fedora core 6 x86_64): mv /var/lib/pgsql/data /data/pg/ ln -s /data/pg/data /var/lib/pgsql/data /etc/init.d/postgresql start I just installed 8.2.2 on a new FC6 machine and when I do that it fails to start. The logs reveal nothing. Smells like a SELinux problem --- you probably need to set the appropriate security context on /data/pg. It might even take an update to the policy ... not sure if the /var/lib/pgsql path is wired into the policy or if it's just driven off file context markers. Look for avc denied messages in the kernel log to get a hint. Or you could just do setenforce 0 but I don't recommend that ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] postgresql fails to start after symlinking pgdata dir
Tom, You were right i just considered that a few minutes ago before getting your response. I set SELINUX=disabled in /etc/sysconfig/selinux and it started up ok. a note in this file: http://pgfoundry.org/docman/?group_id=148 would be handy for those who forget easily and aren't sysadmins :) thanks! On 2/5/07, Tom Lane [EMAIL PROTECTED] wrote: Gene [EMAIL PROTECTED] writes: In the past I've always done the following with success (this is under fedora core 6 x86_64): mv /var/lib/pgsql/data /data/pg/ ln -s /data/pg/data /var/lib/pgsql/data /etc/init.d/postgresql start I just installed 8.2.2 on a new FC6 machine and when I do that it fails to start. The logs reveal nothing. Smells like a SELinux problem --- you probably need to set the appropriate security context on /data/pg. It might even take an update to the policy ... not sure if the /var/lib/pgsql path is wired into the policy or if it's just driven off file context markers. Look for avc denied messages in the kernel log to get a hint. Or you could just do setenforce 0 but I don't recommend that ... regards, tom lane -- Gene Hart cell: 443-604-2679
[GENERAL] accidentally deleted user -- postgres
Hello all, I was recently installing pg on a virtual machine. I also had pg installed and working on my local machine. On the vm I was having some issues installing a Drupal db so in searching for a solution I found a recommendation of the following: net user postgres /delete and then reinstall pg. I ran this command in what I thought was my newly built virtual machine's dos window but it turned out to be the window for my localmachine which had a fully functioning pg install with several dbs. I was able to get my virtual machine up and running with pg and drupal as I was easily able to reinstall pg. The problem I have is I cannot connect to the postgres sql server database using pgadmin III on my localmachine(the one I accidentally ran net user postgres /delete on). If I open up pgadmin and then in left column I have: database below this I have: PostgreSQL Database Server 8.1(localhost:5432)... If I right click on the above and select connect, I get the following error: Server doesn't listen The server doesn't accept connections: the connection library reports could not connect to server: Connection refused (0x274D/10061) Is the server running on host 127.0.0.1 and accepting TCP/IP connections on port 5432? Any Ideas? I am new to PG so perhaps it is a simple adduser statement(I hope)? Thanks in advance. -- View this message in context: http://www.nabble.com/accidentally-deleted-user---%3E-postgres-tf3161276.html#a8768443 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] PostgreSQL/FireBird
RPK wrote: How is FireBird rated when compared with PostgreSQL? Firebird is a good db. But it has its spaces. Its is the little brother of PG. It has two modes: classic (spawn per connection, small caching) and superServer (one program w/threads and lots of caching). superserver does not run well on SMP. If you need SMP run classic. The best part is the maintenance. Its very simple to setup and run and requires no maintenance. I'd say for medium size its great, but for huge, go PG. Firebird does not span disks well (you can have multiple files, but cannot tell what's in each file), no clustering, and poor replication (3rd party only). So, really, it depends on your needs. -Andy ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Postgres SQL Syntax
CREATE TABLE credits (
person integer NOT NULL default '0',
chanid int NOT NULL default '0',
starttime timestamp NOT NULL default '1970-01-01 00:00:00+00',
role
set('actor','director','producer','executive_producer','writer','guest_star','host','adapter','presenter','commentator','guest')
NOT NULL default ''
-- CONSTRAINT chanid_constraint0 UNIQUE
(chanid,starttime,person,role)
-- UNIQUE KEY chanid (chanid,starttime,person,role),
-- KEY person (person,role)
);
I'm doing this table by table, line by line. Each table, I learn
something new about the differences between MySQL and Postgres, I
mentally catalog it and I can always look it up in my own code next time
for examples.
I've a tool that is providing some help but sometimes it chokes. It
choked on this one for example.
I could use some clues as to how to go about converting this MySQL
implementation of roles to Postgres.
So far I've been through 5 tables and it is getting easier but I'm still
getting stuck now and then.
Jim C.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] Postgres SQL Syntax
Besides what Tom says, '0' is a string, not an integer. PG takes it, but it's a bad habit. Maybe it is and maybe it isn't. I wouldn't know. I'm merely the unfortunate soul chosen to convert this from MySQL to Postgres. :-/ I've been working on it for a week now. I've got to say that it pains me to know that there is apparently no open standard in use for importing/exporting data from one db to another. XML would do the job, wouldn't it? If I'm wrong, I sure would like to hear about it. Jim C. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] table updated status
Hi, Can I list tables and the time they were last update (adding columns, drop columns) using sql, something like the ls -l command under unix? Can I compare the table definitions (tables, fields, but not data) of two database using sql, something like the diff file1, file2 command under unix? Thanks, f ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] When to use compound PK instead of a FK?
I typically use compound primary keys when creating a table that represents a many-to-many relationship. I was wondering if anyone else had other situations in which you would use a compound PK? Why do you use it instead of just a foreign key? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Re: Production systems beware: U.S. Daylight Savings Time comes at a new time this year
John D. Burger wrote: Sorry if I'm the only one to find this amusing, but I see that the original message was sent twenty minutes =after= I received it. :) Probably sent from a different time zone. Jim C. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] When to use compound PK instead of a FK?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/04/07 09:48, Jamie wrote: I typically use compound primary keys when creating a table that represents a many-to-many relationship. I was wondering if anyone else had other situations in which you would use a compound PK? Why do you use it instead of just a foreign key? We use multi-segment PKs all the time. Is that what you meant by compound PK? -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFFyBBOS9HxQb37XmcRAiVCAKC6OgW8uBrm5xE9idWrHjzIC/Vv9ACeOM3Q ManPjPaX0eVo+TKmg6YvOQg= =EEH9 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Locking question?
On 1/30/07, Alvaro Herrera [EMAIL PROTECTED] wrote: Shoaib Mir wrote: While debugging an application, I just wanted to confirm from the list here: Suppose I have a long running transaction which has a few updates and inserts running on some specific tables which means it has acquired Exclusive locks too during the transaction on specific table but if just before commit the client app crashes and the commit is never sent, will the Exclusive locks be automatically released? Yes (assuming the backend dies in the process, which may not happen if the app dies silently and while not waiting for anything from the server). Do you mean that the Ex-lock will be held indefinitely in the following situation i) Appln. acquires Exclusive lock. ii) Appln. sleeps or is interacting with human. ii) Appln. crashes. Doesn't the backend kill itself if it detects that the other side of the communincation channel has gone down? -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | yahoo }.com
Re: [GENERAL] Alter definition of a column
On 1/22/07, Jim C. Nasby [EMAIL PROTECTED] wrote: kelly=# update pg_attribute set atttypid=25, atttypmod=-1 kelly-# where attname = 'c1' and attrelid = kelly-# (select oid from pg_class where relname = 'foo'); UPDATE 1 snip Also, you could replace that pg_class sub-select with 'foo'::regclass. Interesting!!! $ edb-psql.exe edb -c select 'pg_class'::regclass; regclass -- pg_class (1 row) $ edb-psql.exe edb -c select 'pg_class'::regclass::int; int4 -- 1259 (1 row) Really interesting!! -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | yahoo }.com
